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.

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.

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

3. Select the Employee table.

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.

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

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

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.

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.

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.

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.

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.

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.

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.