Table Per Type Inheritance in Entity Framework

Table Per Type Inheritance in Entity Framework

The first inheritance
mapping I’m going to
show is called
Table Per Type
or TPT. Before
I start with the example
lets define what is TPT.

Table Per Type Definition

TPT is an inheritance described in the database with separate tables.
Every table provides additional details that describe a new type
based on another table which is that table’s parent.
In the following database ERD the OnlineCourse table is a concrete
type of Course:
 
As you can see the CourseID of both Course and OnlineCourse table
are the same identity.

TPT Example

The following steps will help you to understand how you can create
a TPT inheritance mapping. I’m going to use the exactly database
from the first figure I showed.

Step 1
The first step is to create the Entity Data Model from the database.
Here is the EDM I’m going to use in the example:

As you can see I created an EDM with only the relevant tables for
simplicity. Also, pay attention to the association that was created
(1 - 0..1) between the tables. This is happening because of the use of
the same ID in both of the tables.

Step 2
After we have our EDM ready to work we need to define the
inheritance instead of using the created association.
First remove the association by clicking on the association in
the designer and pressing delete. Then from the designer surface
press on the right mouse button and use the Add –> Inheritance
to open the Add Inheritance dialog.

In the Add Inheritance dialog make the Course the base entity and
the OnlineCourse the derived entity like in the following figure:

We you finish you will have an inheritance arrow between OnlineCourse
and Course like:

Step 3
After we have created the inheritance we need to fix our model.
The CourseID field in the OnlineCourse isn’t relevant anymore since
it is part of the base entity. So lets remove it. Also we need the
CourseID database field to be mapped to something which is the
parent CourseID:

That is it for this kind of model. There will be more model fixing when
we have associations to other entities.

Step 4
Test the created model. The following piece of code will print the
count of the online courses we have in our database:

using (var context = new SchoolEntities())

{

    var query = context.Courses.OfType<OnlineCourse>().Count();

    Console.WriteLine("Number of online courses: {0}", query);

    Console.ReadLine();

}

As you can see I’m using the OfType method which enable me to
do operations on derived types.

Summary

Lets sum up, I showed how to use Table Per Type inheritance in
Entity Framework. As I wrote in a previous post this kind of mapping
isn’t used only by EF and it is also used by other ORMs. In the
next post in this series I’m going to describe and show the Table
Per Hierarchy inheritance.