Todd's World: Entity Framework Modeling: Table Per Type Inheritance

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.

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.

Implementing Table Per Type Inheritance

Step 1: Add the Entity Data Model

Step 2: Generate the Model From The Database

Step 3: Choose the source tables

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:

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.

Step 6: Create Inheritance Relationships

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:

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.

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.

We are done and can now test our model.

Consume the Table Per Type Model

Below is a typical LINQ to Entities Query:

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).

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:

Here is the result:

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.

Published Friday, March 06, 2009 2:44 PM by rob.bagby