A Beginner’s Tutorial on Understanding Table Per Type(TPT) Inheritance in Entity Framework

In this article we will discuss about implementing Table per Type inheritance hierarchy using Entity Framework. We will see a small sample to see how this can be implemented in a step by step manner.

Background

There are times when our database design has tables that do not logically match with the entities that we need in our application. There could be chances that the database has too many or too few tables than the entities required logically by the application. Some other times when the tables are created one per logical entity but the relationship between them is not logical from the application and entity perspective.

Inheritance in entity framework provides a way to create the required logical entities to act on a set of database tables and also to create a more meaningful relationship between entities using inheritance.

There are three type of inheritance relationships in the entity framework. Table per type(TPT), Table per hierarchy (TPH) and Table per concrete type(TPC).

In this article we will try to look at the Table per Type inheritance relationship because from an efficiency standpoint this tends to be the efficient(comparatively) and this provides a great way to model the tables having one to one relationships.

Using the code

The TPT relationship is particularly very useful when we have multiple table with one to one relationships using a foreign key constraint. If we create the entities for these tables the entity framework will generate the default entities with one to one relationship where as this could be modeled better by using inheritance relationship.

Let us try to understand this concept with the help of a small example. Lets say we have a table that keeps track of Bikesdata. This table will keep record of all the Bikes currently in the store. Now the shop also has two more tables for PreOwnedBikesand DiscountedBikes. For this they have created separate tables with the foreign key relationship with the original bikes table. To visualize this design:

Db

Now from our application, If we try to generate an entity model for this design the default entity model generated by the entity framework will look like this:

defaultEntities

This model is ok to work with but looking from the application’s perspective All the PreOwnedBikesand DiscountedBikesare also Bikes. This mean that logically there is an inheritance relationship between them.

So to create the inheritance relationship, let us delete the existing relationship between entities and add inheritance relationship between them(This can be done by right clicking on the entity designer). After making these changed the inheritance relationship between the entities will look like this:

inheritanceEn

When we try to look at the mapping details we can see that the respective tables are mapped to the respective entities. We just created a logical inheritance relationship between them.

Note: If we want all the entities present in Bikes table to be either of PreOwnedBikesor DiscountedBikes, We have to mark the Bikes entity as abstract. For now lets keep it possible to create Bike entity too i.e. It is not abstract.

Now we have entities with some logical relationship and we are ready to work with these entities. Let us now see how we can perform various CRUD operations on these related entities which in turn will update the respective tables.

Insert

Let us start with the insert operation. Lets see how can we add an entity to The PreOwnedBikesentity:

Here we are taking the input from the user and then creating a PreOwnedBikeobject. Then we are adding this to the Bikescollection of entities(Since we have the inheritance relationship, all PreOwnedBikesare Bikes and thus this call will take care of inserting all the data in the respective tables.

In the same way we can insert into the DiscountedBikestable too.

insertTPT

Select

To select the data from the table, we can either select all the data from the table or we an specify a select criteria to select from a table. To select all the data from the respective tables we just need to use the collection properties of the Contextclass.

selectTPT

The other case where we need to select a type of Bikeusing some search criteria we can get the results from the Bikescollection and check for the actual type of entity by using the typeofoperator.

In the above code we are asking the bike’s id from the user and then selecting the Bikeaccordingly. The important thing to note in the above code is that we are getting the details using the base class Bikeand then checking its type using typeofto get the details of the derived entities.

Update and Delete

To update the record, we first need to identify the actual type of the bike, which we can do by using the typeofoperator and then we can update the properties of the bike in the same way as we do with the normal entities.

In the above code we are straight away updating the details associated with the base entity and to update the details associated with the derived entities we are converting the type of appropriate type first by checking the typeofand then changing the properties of the entity.

Delete will also follow the same philosophy and instead of updating the record it will simply delete the record from the table.

updateDeleteTPT

Performing all the above operations will change the data in the respective tables and will also handle the referential integrity among the tables.

Note: Please look at the sample code attached to get the full understanding of the code. The code snippets in this article only shows the code relevant to the topic of discussion.

Point of interest

In this small article we saw how we can create logical relationships between the entities to use them in a better way. We specifically discusses about the Table Per Type Inheritance relationship in this article. To discuss the other relationship types, perhaps, I will write separate articles(to avoid confusion). This aritcle has been written from a beginner’s perspective. I hope this has been somewhat informative.

Download sample code for this article: TPTSample