Demystifying The Code

Entity Framework Modeling: Table Per Type Inheritance

One of the beauties of the Entity Framework is that we are able to model rich conceptual layers that meet the needs of our applications.  Another way of putting this is that we are able to create application-centric views of our data stores.  This conceptual layer may look similar or drastically different from how the underlying data is stored.  Further, we can add in application concepts such as inheritance.  A table-per-type model is a way to model inheritance where each entity is mapped to a distinct table in the store.  Each table contains all of the properties for the entity, as well as a key that maps ultimately to the root table / entity.

View the Screencast

You can watch the channel9 screencast here!

The Data Model

It’s probably best to take a look at a sample data model that we can model a table-per-type hierarchy from.

image

The motivation for building a data model such as this should be pretty clear.  Essentially it comes down to protecting data.  Suppose that we have business rules that state that every Student have a degree defined.  We would like to set this column as non-nullable.  However, if Students were stored in a generic People table along with Admins and Instructors, this would not be possible.  This is because the degree column would have to be nullable because Admins and Instructors do not have degrees (in the case of this example).  By storing the Student, Admin, Instructor and BusinessStudent information in their own tables, we are able to use nullability to enforce certain business rules. 

As you might imagine (from the name), we will be developing a conceptual layer that has an entity that maps back to each table in the above screenshot.  We will also be modeling an inheritance hierarchy.  In this case, we will have a base entity of Person.  We will have 2 entities that inherit from person: Student, Admin and Instructor.  We will then have a BusinessStudent entity that inherits from Student.

Entity Framework Training

Implementing Table Per Type Inheritance

Step 1: Add the Entity Data Model

image

Step 2: Generate the Model From The Database

image

image

Step 3: Choose the source tables

image

Step 4: Name the entities appropriately

You should give the entities and entity set appropriate names.  Here are the names I chose:

  • Person
  • Student
  • Instructor
  • Admin
  • BusinessStudent

I further named the entity set for Person to People.  We do not need to name the entity set names for the other entities due to our inheritance hierarchy we will be modeling in the next step. 

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:

image

Step 5: Delete the default relationships

We need to delete the relationships that were created by default, as we want to model an inheritance relationship.

image

Step 6: Create Inheritance Relationships

image

image

You need to do this for each derived type (Student, Instructor, Admin and BusinessStudent) in our example.  BusinessStudent will have Student as it’s base entity.  When you are done, the designer should look like this:

image

Step 7: Delete the derived-entities key properties

When we added the tables, the designer looked at the table schema and added properties for each table column.  That is all well and good, however, it added a property for the PersonID column.  The PersonID is a key between the entities and will be inherited from the base entity.  Therefore, we do not need this property set on each of the derived entities.  Simply single-click on PersonID on each of the derived entities and press delete.

image

Step 8: Map the PersonID column to the inherited PersonID property

In step 6, we deleted the PersonID property on the derived entities, because they are inheriting this property from the base.  Because we deleted those properties, the PersonID column from the underlying table is no longer mapped.  We need to map it to the inherited PersonID property.  Simply choose the PersonID from the dropdown box.

image

We are done and can now test our model.

Consume the Table Per Type Model

Below is a typical LINQ to Entities Query:

image

However, now we can take advantage of our inheritance hierarchy.  Perhaps we want to only return BusinessStudents and we want to access some BusinessStudent-specific properties.  We could now specify only BusinessStudent entities are returned and now we have access to BusinessStudent-Specific properties (along with properties inherited from Person and Student).

image

Alternatively, you could have left the query as-is, pulling all People.  You could then check the type of the entity before determining your action.  For instance, in our demo we could write out the FirstName and LastName for all people, but if the entity is a Student or BusinessStudend, we could write out more specific information:

image

Here is the result:

image

Conclusion

The Entity Framework provides powerful inheritance modeling capabilities, including the ability to model table-per-type inheritance.  I hope this post was helpful in illustrating this.

Comments

6 Responses to “Entity Framework Modeling: Table Per Type Inheritance”
  1. Manitra says:

    Hello,

    Thanks for this great article, it’s an excellent start point for dealing with inhenritance in the entity framework.

    Manitra.

  2. Daniel says:

    Excellent post! Very useful and easy to understand.
    I followed and all seems to work. However when I validated the Model I get an Error 3034 saying that the column PersonID from Person is mapped by three entities with different key. (I suppose those three entities are Student, Instructor and Admin) and that I must ensure those doesn’t overlap
    Do you know why is that happening?

  3. David says:

    Thanks, it was very helpful

  4. admin says:

    You need to delete the key property from the 2 derived types.

  5. Nauman Ahmed says:

    thanks, very descriptive.

  6. Matt Penner says:

    Hey Rob, I needed to implement inheritance on a few of my entities and found this post. It was great! Straight to the point and very easy to follow. I already had a lot of data in the production database so if only migrating the data with the new keys and dependencies was as easy. :)

    Anyway, it all worked out great. Thanks!

Speak Your Mind

Tell us what you're thinking...
and oh, if you want a pic to show with your comment, go get a gravatar!

Demystifying The Code