Entity Framework 6 Recipes 2nd Edition (10-6), entityrecipes

Entity Framework 6 Recipes 2nd Edition (10-6), entityrecipes

10-6. Use stored procedures in The TPT inherited model

Problem

To use stored procedures in a TPT inheritance model

Solution

Suppose there is a model shown in Figure 10-6. in the model, Magazine (Magazine) and DVD inherit from the base Media (Media, Note: The table name in the sample database is actually: Medium. You are working on the example or the following code to see Medium, identify by yourself ). in the database, each object has a table. We use the TPT method to model some tables. we want to use a stored procedure to obtain data from the database for these models.

 

Figure 10-6.A model using Table per Type inheritance. The model represents some information about magazines and DVDs

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

■Tip: If you need to reviewWhat is the impact of TPT mode on its performance? See section 2-8 in chapter 2nd

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

Next, create a stored procedure and use it to obtain objects.

1. Create a stored procedure in the database, as shown in Listing 10-15.

Listing 10-15.The GetAllMedia Stored Procedure That Returns a Rowset with a Discriminator Column

Create procedure [Chapter10]. [GetAllMedia]

As

Begin

Select m. MediaId, c. Title, m. PublicationDate, null PlayTime, 'magazine' MediaType

From chapter10.Media c join chapter10.Magazine m on c. MediaId = m. MediaId

Union

Select d. MediaId, c. Title, null, d. PlayTime, 'dvd'

From chapter10.Media c join chapter10.DVD d on c. MediaId = d. MediaId

End

2. Right-click the model design view, select "update model from database", and select the Stored Procedure GetAllMedia. Click "finish"

3. (Note: My environment is win10 + vs2013 + ef6.1.3. This step is not required, and this step has been completed in step 1, only the final "return the following content set" must be modified.) Right-click the design view of the model and select "add" transform function import. select GetAllMedia from the stored procedure/function name drop-down box. in the "Function Import Name" text box, enter GetAllMedia. this is the method name in the model. select "entity" in "Returned content set" and select Media from the drop-down list. click OK ". the <FunctionImportMapping> framework will be created.

4. Right-click the. edmx file and select "open mode" under ➤ ing section of "Edit. edmx File ".

The <FunctionImportMapping> label of is matched with the code shown in Listing 10-16 (because the names of EF6RecipesModel may be different from those of your example ). it maps the attributes of the columns returned by the stored procedure to the objects of the Media type.

Listing 10-16.This FunctionImportMapping Conditionally Maps the Returned Rows to Either

Magazine or the DVD Entity.

<FunctionImportMapping FunctionImportName = "GetAllMedia" FunctionName = "EF6RecipesModel. Store. GetAllMedia">

<ResultMapping>

<EntityTypeMapping TypeName = "EF6RecipesModel. Magazine">

<ScalarProperty ColumnName = "PublicationDate" Name = "PublicationDate"/>

<Condition ColumnName = "MediaType" Value = "Magazine"/>

</EntityTypeMapping>

<EntityTypeMapping TypeName = "EF6RecipesModel. DVD">

<ScalarProperty ColumnName = "PlayTime" Name = "PlayTime"/>

<Condition ColumnName = "MediaType" Value = "DVD"/>

</EntityTypeMapping>

</ResultMapping>

</FunctionImportMapping>

5. Use the code shown in Listing 10-17 to call the Stored Procedure GetAllMedia using the GetAllMedia () method.

Listing 10-17.Using the GetAllMedia Stored Procedure via the GetAllMedia () Method

Static void Main (string [] args)

{

Using (var context = new EFRecipesEntities1006 ())

{

Context. Media. Add (new Magazine

{

Title = "Field and Stream ",

PublicationDate = DateTime. Parse ("6/12/1945 ")

});

Context. Media. Add (new Magazine

{

Title = "National Geographic ",

PublicationDate = DateTime. Parse ("7/15/1976 ")

});

Context. Media. Add (new DVD

{

Title = "Harmony Road ",

PlayTime = "2 hours, 30 minutes"

});

Context. SaveChanges ();

}

Using (var context = new EFRecipesEntities1006 ())

{

Var allMedia = context. GetAllMedia ();

Console. WriteLine ("All Media ");

Console. WriteLine ("========= ");

Foreach (var m in allMedia)

{

If (m is Magazine)

Console. WriteLine ("{0} Published: {1}", m. Title,

(Magazine) m). PublicationDate. to1_datestring ());

Else if (m is DVD)

Console. WriteLine ("{0} Play Time: {1}", m. Title, (DVD) m). PlayTime );

}

}

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

Console. ReadKey ();

}

The output result is as follows: Listing 10-17:

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

All Media

==========

Field and Stream Published: 6/12/1945

National Geographic Published: 7/15/1976

Harmony Road Play Time: 2 hours, 30 minutes

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

How does it work?

The solution has two key points: Identifying column injection to the result set and using condition ing to the result entity.

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

■Note::The validation column is a database metadata column used to specify the object type..

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

The stored procedure shown in 10-15 combines records obtained from Magazine and with records obtained from the DVD table, then, inject the table into the identification columns of media types such as Magazine or DVD. for each select, we connect the Media table that represents the model base class to include the Title column. all records from these three tables are included in the result set and contain tags from a specific table. based on the record tags (Magazine or DVD), we map them to Magazine or DVD entities respectively. this operation is completed in the <FunctionImportMapping> section ,.

When we added the function import, we added the GetAllMedia () method to call the GetAllMedia stored procedure. In Listing 10-17, we call this method. Then, the entire object graph is fully instantiated by the inheritance structure. We traverse this object set and print out Magazine and DVD objects alternately.