Entity Framework 6 Recipes 2nd Edition (10-7) tph using stored procedures in the inheritance model

10-7. Using stored procedures in the TPH inheritance model

Problem

Use a stored procedure to populate an entity with the TPH inheritance model

Solution Solutions

Assume that the model is already shown in Figure 10-7. We have two derived entities: Instructor (faculty) and student (student).

This model uses the TPH inheritance method, so there is only one table in the database. The person table has an authentication column that maps the table's records to different derived entities. We want to populate the entity with a stored procedure.

Figure 10-7. A model for instructors and students

Use the following procedure to achieve the purpose of returning an entity with a stored procedure:

1. Create a stored procedure in your database as shown in Listing 10-18. This stored procedure returns all people.

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 view of the model and select Update model from database. Select the stored procedure getallpeople. Click Finish to add the stored procedure to the model.

3. My environment is win10+vs2013+ef6.1.3, do not need this step, the 1th step has already completed this step, but the final "return to the following collection" must be modified), right click on the Design view of the model, select "New"? Function Import. Select Getallpeople from the stored procedure/Function Name drop-down box. In the Function Import Name text box, enter: Getallpeople. This is the name of the method in the model. In the "return to the following collection" tick "entity", select person in the drop-down box. Click OK. The <FunctionImportMapping> framework will be created.

4. Right-click the. edmx file and select Open with? XML Editor. Edit the. edmx file under the mapping section

<FunctionImportMapping> tags, with Listing 10-19 code to match (because a name like Ef6recipesmodel may be different from your example). It maps the properties of the columns returned by the stored procedure with the entity of the person type.

(Note: The PersonType column in the given sample database, type varchar, cannot be empty.) I followed the steps here to do a bit, after the operation found that there are errors. Read the original book site downloaded code found this column nvarchar, and can be empty, so after changing the database, and then "update the model from the database" once, you can. ）

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>

5. Follow the pattern in Listing 10-20 to use the Getallpeople stored procedure via the

Getallpeople () method

Listing 10-20. Querying the Model Using the Getallpeople Stored Procedure via

The Getallpeople () Method.

static void Main (string[] args)

{

using (var context = new EFRecipesEntities1007 ())

{

Context. People.add (New Instructor

{

Name = "Karen Stanford",

Salary = 62500M

});

Context. People.add (New Instructor

{

Name = "Robert Morris",

Salary = 61800M

});

Context. People.add (New Student

{

Name = "Jill Mathers",

degree = "Computer Science"

});

Context. People.add (New Student

{

Name = "Steven Kennedy",

degree = "Math"

});

Context. SaveChanges ();

}

using (var context = new EFRecipesEntities1007 ())

{

Console.WriteLine ("Instructors and Students");

var allpeople = context. Getallpeople ();

foreach (var in allpeople)

{

If (person is instructor)

Console.WriteLine ("Instructor {0} makes {1}/year",

Person. Name,

((instructor) person). Salary);

else if (person is Student)

Console.WriteLine ("Student {0} ' s major is {1}",

Person. Name, ((Student) person). degree);

}

}

Console.WriteLine ("\npress any key to exit ...");

Console.readkey ();

}

Output results such as listing 10-20:

===================================================================

Instructors and Students

Instructor Karen Stanford makes $62,500.00/year

Instructor Robert Morris makes $61,800.00/year

Student Jill Mathers ' s major is computer

Student Steven Kennedy ' s major is Math

=================================================================================

How it works

Populating the TP inheritance model with a stored procedure is simpler than TPT (see section 10-6). The stored procedure here simply retrieves all the rows of the table person. We are in the <FunctionImportMapping> tag of the relationship (column name + value to determine is a certain entity), (see Listing 10-19), that is, the table identification column (PersonType) of the different values, into the corresponding entity

We do this in section 10-6, and we do so in this section.

Attached: Creating a script file for the database used by the sample

Entity Framework 6 Recipes 2nd Edition (10-7) tph using stored procedures in the inheritance model