Single Table Inheritance in Entity Framework

In this blog posting, I will do a walk through of how to apply single table inheritance using entity framework. Entity framework supports 3 different models of inheritance.

  1. Table Per Hierarchy (Single Table Inheritance)
  2. Table Per Type
  3. Table Per Concrete Class

Of all these supported inheritance models, the most simplest and easiest to implement is Table Per Hierarchy (Single Table Inheritance). To implement this inheritance, you store all concrete types in one table. In Entity framework to identity a row as a specific concrete type, you define a discriminator column which identities which concrete type a specific row gets mapped to. From a usability point, I have found Single table model to be very easy to get started. However from the database perspective, the model doesn't seem to favor a clean approach. The reason is, you are storing all different concrete types in a single table. Some concrete types would need certain columns where as others won't. To accomplish flexibility at the table level, you have to mark all columns that are specific to their concrete implementation as allow nulls. Some database developers may find this approach not a good solution because it does not efficient use of disk space. Let's walk through an example to see how we can use the entity framework designer to apply single table inheritance.

To demonstrate the example, I will create a simple table called Employees. In the employees table we will capture two kinds of Employee, Hourly Worker and Salaried Employee. For both employees, we would like to know about  their Name, Phone,Email. For Hourly Employee, we also want to capture their hourly rate and the number of Hours worked. For salaried employee, we would like to know their Salary.

image

The above employee schema shows that we have Name, Phone and Email as required because that is required for both types for Employees. However Salary, Rate, Hours are not required as they are specific fields for each concrete type. The last column type is the discriminator column that identifies what type of row it is; is it Hourly Employee or Salaried Employee?

We will start with adding a new Ado.net Entity data model.

image

2. Select the database and save the connection string to app.config file.

image

3. Select the Employee table.

image

After clicking finish, you end up with a single table Employee on the Entity framework designer. Now we will add two more entities that inherit from Employee entity; Hourly Employee and Salaried Employee.

4. Add Hourly Employee to the designer, inheriting from Employee class.

image

4. Add Salaried Employee to the designer, inheriting from Employee class.

image

5. After creating both concrete classes your entity diagram should look like this.

image

From the above diagram, you will notice that Salary, Rate and Hours are not attributes specific to Employee class. So let's move those properties to their respective class by cutting and pasting the properties to each of their classes.

image

Now that we have our properties in their respective classes, we can map Salary property on Salaried Employee class to Salary column in Employee table. We will also specify how Salaried Employee will map to Salary table by using condition column.

image

In the above example, I am setting a condition that when Type column on the Employee table has a value of SE (Salaried Employee), it should be mapped to Salaried Employee entity. Additionally I am also mapping Salary property to Salary column in the Employee table.

5. Next we will map Hourly Employee class Employee table using the discriminator column and mapping additional columns specific to Hourly Employee.

image

In the above diagram, I am setting a condition of Hourly Employee where type is equal to HE. Furthermore, I am also mapping additional fields specific to Hourly Employee class.

At this point we are not done because if you try to compile the project you will get build errors and those build errors would be something in the line of Type property not being mapped. The error is not very clear but in order to clear this error you have to remove the Type column from the base class because the column that you are using as discriminator column cannot be mapped.

After removing the Type column from Employee base class, you are still not done. If you try to compile the project, you will get the error below.

image

Once again the error is very vague and does not explain how to overcome this error. What it means is, we have mapped our concrete classes, Hourly Employee and Salaried Employee using the Type column attribute but we have not identified how to map the base Employee class. Either we can map the Employee class to a specific value in Type column but in my case, Employee class is really a base class for both Hourly and Salaried Employee and it does not map to any specific implementation. Therefore I can mark my Employee class as an abstract class to get rid of compile time error as shown below.

image

Once we mark the Employee class as abstract, the error goes away because we are explicitly stating that Employee class cannot be instantiated therefore it cannot be mapped to any concrete implementation.

Now all this required is to write a query that fetches Hourly and Salaried Employee as shown below.

image

In the above code, to access the Hourly Employee, I am making use of OfType operator specifying Hourly Employee as a generic type to retrieve only Hourly Employee. To fetch the salaried employee, I pass in Salaried Employee as my generic type. Output below shows the results of running the query.

image

Published Saturday, August 16, 2008 7:42 PM by zhirani

Comments

# re: Single Table Inheritance in Entity Framework

Thursday, August 21, 2008 6:15 AM by Alexnaldo Santos

Hi,

Can you please create an example using "2. Table Per Type" ?

Tkanks.

# re: Single Table Inheritance in Entity Framework

Friday, August 22, 2008 6:06 PM by Dan

Best walk-though of this I've seen.  I wouldn't have been able to determine that I needed to delete the discriminator column from the base type without this post.  Thanks!

# re: Single Table Inheritance in Entity Framework

Sunday, August 24, 2008 5:25 AM by Mike.Borozdin

Excellent post! Keep it up!

# re: Single Table Inheritance in Entity Framework

Wednesday, August 27, 2008 6:15 PM by Spencer

Very helpful and works great as far as it goes, but when I try to create an association between a descendant and another class, it fails.  I have two fields in my table, entity_type and entity_id.  The entity_id is a key to the other table.  So, extending your example, if there were a class for Office and a class for Cubicle, then an Office has an association to the SalariedEmployee and the Cubicle has an association to the HourlyEmployee.  I can't seem to get these to map and am stuck.  I get an error about Multiplicity is not valid...  It makes sense, since it is not allowing me to map the entity_id field--it keeps mapping the id for each table, which is wrong.

# re: Single Table Inheritance in Entity Framework

Tuesday, September 16, 2008 11:39 AM by Robert

Your writing style and use of VS2008 screen shots is great!

Any chance you could apply your skills to create the Table Per Type example?

# re: Single Table Inheritance in Entity Framework

Wednesday, October 15, 2008 2:41 AM by Chris

I want map an m:n relationship. For example i have a Company(id, name) and a CompanyType(id, typeName) relation and a Company_CompanyType(CompId, CompTypeId) relation(in the DB).

Now i want to have the classes Company, Supplier, Purchaser. (Supplier have all Company Properties but it is associate with CompanyType.name = SUPPLIER)

I can't find the right solution for it. Have your a hint for me?

Thanks

# re: Single Table Inheritance in Entity Framework

Thursday, September 17, 2009 7:01 PM by SamerPaul

Thanks for this post! This is exactly what I've been looking for the past few days. Very useful

# re: Single Table Inheritance in Entity Framework

Thursday, February 4, 2010 2:58 PM by Paul

Hi,

I know I cannot assign Stored Procedure to the abstract calss. I would like to know how to use stored procs to do inserts/Updates in this TPH Model.

Thank you.

# re: Single Table Inheritance in Entity Framework

Tuesday, February 8, 2011 12:22 AM by CeeBee

Have any of you ever actually tried to compile this stuff?  None of it works...not one single example posted anywhere...The Table Per Heirarchy strategy in visual studio creates a discriminator column that you can not override.

# re: Single Table Inheritance in Entity Framework

Wednesday, April 6, 2011 3:20 AM by Allen.Lian

Hi, I have a question about the inheritance.

If I want to get the number of the employees, regardless of the employee type, how will I handle it?