Entity Framework Modeling: Table Per Hierarchy Inheritance

In a previous post, I illustrated how you can implement table-per-type inheritance in your model.  You use table-per-type if your data model splits information for different logical things between tables that have 1:1 relationships.  Some data models store multiple logical things in one table and use a discriminator column to advertise what kind of thing it is (please excuse the use of the word ‘thing’ – all of the good words like entity and type have alternate meanings that could cause confusion).  With data models such as these, you can implement table-per-hierarchy inheritance.  With table-per-hierarchy, you can model the same type of inheritance between entities as in table-per-type.  The difference is that all of the entities are sourced from a single table, with the discriminator column being used as the differentiator.

Watch the screencast

You can watch the screencast here.

Examining the Data Model

As discussed earlier, this data model only has 1 table.  I have annotated the model to illustrate which columns are associated with each type of person (Student, Instructor, Admin and BusinessStudent).  I have further pointed out that PersonCategory is the discriminator column.  If this column contains a 1, it indicates that the person is a student, a 2 indicates an instructor, a 3 indicates an admin, while a 4 indicates that the person is a business student.

From this model, we want to create an entity hierarchy with a Person entity being the base.  We then want to create 3 entities (Student, Instructor and Admin) that inherit from Person.  We then want to create a BusinessStudent that inherits from Student.  The resulting hierarchy will be exactly like the hierarchy I created in this post.

Implementing Table Per Hierarchy

Step 1: Add the Entity Data Model

Step 2: Generate the Model From The Database

Step 3: Choose the source table

We only need the People table here.  It contains records for each of our entity types

Step 4: Name the Person entity appropriately

You should give the entities and entity set appropriate names.  I named the entity Person and changed the EntitySet name to People.  The easiest way to rename the entities and entity sets is to single-click on the entity in the designer and make the updates in the properties pane:

Step 5: Add the derived entities

We now need to add our derived entities.  We want to add 4 new entities in all: a Student, Instructor, Admin and BusinessStudent.  For the first 3, name them appropriately and set the Base type to Person.  For BusinessStudent, set the Base type to Student.

Your model should now look like this:

Step 6: Cut and paste the properties from the Person entity to the appropriate entities.

Simply single-click on the property in the Person entity and press ‘ctrl + x’.  Then single-click on the Scalar Properties section of the appropriate entity and press ‘ctrl + v’. The properties to cut and paste are as follows:

• HireDate –> Instructor
• EnrollmentDate –> Student
• AdminDate –> Admin
• Credits –> Student
• Degree –> Student
• BusinessCredits –> BusinessStudent
• Discipline –> BusinessStudent

Step 7: Delete the discriminator column property from Person

You need to delete the PersonCategory property that the designer created in the Person entity.  This column is used as the discriminator.  Simply single-click on the PersonCategory property in the Person entity and press the delete key.

The model should now look like this:

Step 8: Map the new derived entities to the Person table and set the discriminators

Now we need to set the discriminator by adding a condition:

Once you have mapped all 4 entities to the person table and have set a condition on all 5 entities (including the Person entity), you are done and ready to exploit this model.

Consuming the Model

Results:

Conclusion

As you can see, we have built a complete inheritance hierarchy that sources from a single table.  In fact, from an application standpoint, the hierarchy is exactly the same as the one we built in our table-per-type example.