Beyond the Basics with Modeling and Inheritance - Entity Framework 6 Recipes - page 203

10. Repeat step 9 for the Instructor entity. Be sure to map the result columns StaffId and

InstructorId from the Insert action.

Right-click the .edmx file in the Solution Explorer, and select Open With ➤ XML Editor. This will close the
designer and open the .edmx file in the XML editor. Scroll down to <EntityContainerMapping> tag in the mapping
layer. Insert the QueryView in Listing 6-21 into the <EntitySetMapping> tag.

Listing 6-21. QueryView for the Instructor and Principal Entities

<EntitySetMapping Name="Staffs">
<QueryView>
select value
case
when (i.StaffId is not null) then
EFRecipesModel.Instructor(s.StaffId,s.Name,i.InstructorId,i.Salary)
when (p.StaffId is not null) then
EFRecipesModel.Principal(s.StaffId,s.Name,p.PrincipalId,p.Salary,p.Bonus)
END
from EFRecipesModelStoreContainer.Staff as s
left join EFRecipesModelStoreContainer.Instructor as i
on s.StaffId = i.StaffId
left join EFRecipesModelStoreContainer.Principal as p
on s.StaffId = p.StaffId
</QueryView>
</EntitySetMapping>

How It Works
With Table per Type inheritance, Entity Framework requires that the foreign key for the base entity's table be the
primary keys in the derived entity's table. In our example, each of the tables for the derived entities has separate
primary keys.
To create a Table per Type inheritance model, we started at the conceptual level by deriving the Principal and
Instructor entities from the Staff entity. Next we deleted the mappings that were created when we imported the table.
We then used a QueryView expression to create the new mappings. Using QueryView pushed the responsibility for the
Insert, Update, and Delete actions onto our code. To handle these actions, we used traditional stored procedures in
the database.
We used QueryView to supply the mappings from our underlying tables to the scalar properties exposed by our
derived entities. The key part of the QueryView is the case statement. There are two cases: either we have a Principal
or we have an Instructor. We have an Instructor if the Instructor's StaffId is not null, or we have a Principal if the
Principal's StaffId is not null. The remaining parts of the expression bring in the rows from the derived tables.
The code in Listing 6-22 inserts a couple of Principals and one Instructor into our database.

Listing 6-22. Inserting into and Retrieving from Our Model

using (var context = new EF6RecipesContext())
{
var principal = new Principal { Name = "Robbie Smith",
Bonus = 3500M, Salary = 48000M };
var instructor = new Instructor { Name = "Joan Carlson",
Salary = 39000M };