A Beginner’s Tutorial on Understanding Table Per Hierarchy(TPH) Inheritance in Entity Framework

In this article we discuss about the Table per Hierarchy inheritance relationship using entity framework. We will 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).

Earlier we have discussed about TPT Inheritance. Now in this article we will discuss about the Table per Hierarchy inheritance relationship i.e. TPH.

Using the code

TPH inheritance actually keeps only one database table for all the classes in the hierarchy. This solution is not recommended from a database perspective because using this solution would require the underlying table design in such a way that it will not be normalized and infact will have a lot of redundant columns(the number of redundant columns will depend on number of derived classes, We will see that in a moment).

Now to understand this concept let us try to implement a similar project (like we did it in TPT article) but this time instead of keeping a record of Cars, lets design the solution for Cars. Lets say we have a table that keeps track of Carsdata. This table will keep record of all the Cars currently in the store. Now the shop also has provision for PreOwned cars and Discounted cars. For this they have created columns in the same cars tables. To distinguish the actual car type the columns related to other type will be nulli.e.

tphDb

In the above table is the car is of Preownedtype then the data in YearsOldcolumn will have some value but the DiscountRatecolumn will be null and same is the case for Discounted cars. So we can see how this is a not an efficient and normalized solution from a database perspective.

Nevertheless, Let us try to model this table using entity framework in our solution. When we add an entity data model for this table we will get the following default entity created.

tphDefault

Now we don’t want this bad table design to propagate to our application(otherwise too many workarounds will creep up with time). So we break this into a better logical design. We create two more Entities for DicountedCarsand PreOwnedCars. We will then create an inheritance relationship between then where Carwill be the base entity for both these entities. Finally, we remove the respective properties from the Carentity and move them to these newly created entities. (All this can be done by using the context menu on entity designer). The resulting entities will look like:

tphInheritanceEn

Next thing is that we need to define the table mapping and mapping conditions for these two entities. We want the entities to push the data to their respective columns in table and will ensure that the columns pertaining to the other entities are null. The table mappings for the DiscountedCarentity:

mappingDiscounted

The table mapping for the PreOwnedCarentity:

mappingPreOwned

The last thing we need to do is that we need to mark the Carentity as abstractso that nobody should be able to able to create a Cartype without creating the concrete type.

Now we have entities with required 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

Insert

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

Here we are taking the input from the user and then creating a PreOwnedCarobject. Then we are adding this to the Carscollection of entities(Since we have the inheritance relationship, all PreOwnedCarsare Cars and thus this call will take care of the rest.

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

InsertTPH

Select

To select the data from the table, we can either select all the data from the table or we can 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.

seelctTPH

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

In the above code we are asking the car’s id from the user and then selecting the Caraccordingly. The important thing to note in the above code is that we are getting the details using the base class Carand 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 car, which we can do by using the typeof operator and then we can update the properties of the car 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.

updateDeleteTPH

Performing all the above operations will change the data in the the single Cartable even though from our application we are acting upon various logical entities, physically they all are using a single Carstable for the data operations and thus Table per Hierarchy.

Note: Please look at the sample code 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 Hierarchy Inheritance relationship in this article. This aritcle has been written from a beginner’s perspective. I hope this has been somewhat informative.

Download sample code for this article: TPHSample