Stored Procedures - Entity Framework 6 Recipes - page 355

To create and use a stored procedure that returns these entities, do the following:

1. In your database, create the stored procedure in Listing 10-18. This stored procedure

returns all of the people in the hierarchy.

Listing 10-18. The GetAllPeople Stored Procedure, Which Returns All the People, Both
Students and Instructors, in the Model

create procedure [Chapter10].[GetAllPeople]
as
begin
select * from chapter10.Person
end

2. Right-click the design surface, and select Update Model from Database. Select the

GetAllPeople stored procedure. Click Finish to add the stored procedure to the model.

3. Right-click the design surface, and select Add ➤ Function Import. In the dialog box, select

the GetAllPeople stored procedure. Enter GetAllPeople in the Function Import Name
text box. Select Entities as the type of collection and Person as the type of entity returned.
Click OK. This will create the skeleton <FunctionImportMapping> section.

Right-click the .edmx file, and select Open With ➤ XML Editor. Edit the
<FunctionImportMapping> tag in the mapping section of the .edmx file to match the
code in Listing 10-19. This maps the rows returned by the stored procedure either to the
Instructor or to Student entity based on the PersonType column.

4.

Listing 10-19. The FunctionImportMapping Conditionally Maps Rows to Either the
Instructor or Student Entity

<FunctionImportMapping FunctionImportName="GetAllPeople"
FunctionName="EF6RecipesModel.Store.GetAllPeople">
<ResultMapping>
<EntityTypeMapping TypeName="EFRecipesModel.Student">
<ScalarProperty Name="Degree" ColumnName="Degree" />
<Condition ColumnName="PersonType" Value="Student"/>
</EntityTypeMapping>
<EntityTypeMapping TypeName="EF6RecipesModel.Instructor">
<ScalarProperty Name="Salary" ColumnName="Salary"/>
<Condition ColumnName="PersonType" Value="Instructor"/>
</EntityTypeMapping>
</ResultMapping>
</FunctionImportMapping>

Follow the pattern in Listing 10-20 to use the GetAllPeople stored procedure via the
GetAllPeople() method

5.

Listing 10-20. Querying the Model Using the GetAllPeople Stored Procedure via
the GetAllPeople() Method.

using (var context = new EF6RecipesContext())
{
context.People.Add(new Instructor { Name = "Karen Stanford",
Salary = 62500M });
context.People.Add(new Instructor { Name = "Robert Morris",
Salary = 61800M });