Table per Hierarchy (Walkthrough)

Console.WriteLine("Title {0} Resource {1}", vid.Title, vid.ResourcePath);

}

Screen shot below shows the output of the above code on Console window.

5.1.2 Table per Hierarchy (Walkthrough)

Problem: You have an employee table in the database which contains two kinds of Employee; Hourly Employee and Salaried Employee. To identity each type of Employee, the table has an additional column Employee Type which can have two values; HE for Hourly Employee and SE for Salaried Employee. You want to map this table structure to entity data model using Table Per Type.

Solution: Using the import wizard will allow you to import the tables into the entity data model. After the import is completed you will see Employee entity created on the designer. Since employee will have two derived types, create two entities Hourly and Salaried Employee that inherits from Employee

entity. Move the properties specific to the derived type to its own entity. Then map each derived type to the employee table and map properties on the

derived type to columns on the employee table. Since employee base class does not have any definition to our concrete implementation except for the fact that it serves as our base class, we need to ensure that employee class cannot be instantiated. To satisfy the requirements make the base class abstract. If we do not make the base class abstract, Entity framework

validation will complain that Employee class needs to also contain some discriminator value that it can map to.

Discussion: In this walk through we will go through the steps of how to accomplish table per hierarchy using the existing employee table defined on the database. Screen shot below shows the employee table.

The Employee table contains all the columns required for each type of

Employee. Since both types of Employee are in same table, we need to make columns specific to each Employee as allow nulls. Employee table also contains a Type column which is used to differentiate between each type of Employee. To import the table into entity data model, we will use Update Model from Database option. Screen shot below show the entity created on the design surface when we imported Employee table.

The next step is to create two entities, Hourly and Salaried Employee and move the fields’ specific to each type to its own entity. One of the ways you can move fields is by cutting the fields from the base class Employee and pasting it to derived entity. To create an entity on the design surface, right click and choose Add entity. This will open up Add entity dialog which would allow us to give the entity a name and choose the base class the entity derives. For Hourly Employee, I have chosen the following settings.

The process of Salaried Employee is similar. After setting up the inheritance move the fields on the Employee class to its derived type. Figure below show the model after moving the appropriate fields to their entities.

Since we will be using Type column as a discriminator to determine what type of entity to instantiate, the column cannot be mapped to a property.

Therefore we need to remove the Type column from Employee base class. To map the Hourly Employee to value on the Type column, select the table mapping for Hourly Employee, choose Employee table and add a condition where Type is equal HE for Hourly Employee. Figure below shows the mapping for Hourly Employee.

Similarly for Salaried Employee, select Employee table and set the condition for Type column with value of SE for Salaried Employee. Figure below shows the completed mapping for SalariedEmployee.

If you try to validate the model at this time, you will get the following error.

Error 1 Error 3023: Problem in Mapping Fragments starting at lines 139, 144, 149:

Column Employees.Type has no default value and is not nullable. A column value is required to store entity data.

This error is caused because we have not specified any Type value for

Employee base class. If Employee entity cannot be mapped to a certain value and is only a base class, then we must set the Employee entity as an abstract class to clear the error. After making Employee entity abstract, the model should validate cleanly. The final model is shown below

To test the model, we create an object context, add different types of

Employees and confirm that records got written to appropriate table. In the code below I am creating two types of Employees and adding them to

AddToEmployees followed by SaveChanges. To confirm the insert happened successfully, using the second data context, I am retrieving each type of

Employee and printing the properties of entity on the console window. Notice that our context does not expose any collection of derived types. The object context only exposes Employees collection which contains an entity of type Employee. To access a derived type, we need to use OfType operator passing in the derived type you needed returned.

var db = new STPTInh();

var hourly = new HourlyEmployee { Name = "Alex", Rate = 40, Hours = 40 };

var salaried = new SalariedEmployee { Name =

"Chris", Salary = 90000 };

db.AddToEmployees(hourly);

db.AddToEmployees(salaried);

db.SaveChanges();

var db2 = new STPTInh();

var huorly1 =

db2.Employees.OfType<HourlyEmployee>().First();

Console.WriteLine("Name {0} Rate {1} Hours {2}",huorly1.Name,huorly1.Rate,huorly1.Hours);

var salary1 =

db2.Employees.OfType<SalariedEmployee>().First();

Console.WriteLine("Name {0} Salary {1}",salary1.Name,salary1.Salary);

Screen shot below shows the property values for employee printed on the console window.

5.1.3 Extending Table per Type with Table per Hierarchy