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

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

10-5. Use custom functions in the storage model

Problem

You want to use udfs in the model instead of stored procedures.

Solution

Assume that our database has a member (members) and a messages data table, as shown in Figure 10-4:

 

Figure 10-4.A simple database of members and their messages

 

In this case, we may not allow entry-level programmers to create stored procedures in the database. However, we want to encapsulate the business logic of members and the messages they send with the most, in this case, we can use user-defined functions in the model.

Our model is shown in Figure 10-5:

 Figure 10-5.The model for members and their messages

Next, define the function To define the custom function in the storage model, do the following:

1. right-click. select the open method XML (text) Editor for the edmx file. in this way. open the XML editor of The edmx file. add the code in Listing 10-13 to the <Schema> element ). this is the definition of the function.

Listing 10-13.The Definition of the Custom Function MembersWithTheMostMessages

<Function Name = "MembersWithTheMostMessages" IsComposable = "false">

<CommandText>

Select m .*

From chapter10.member m

Join

(

Select msg. MemberId, count (msg. MessageId) as MessageCount

From chapter10.message msg where datesent = @ datesent

Group by msg. MemberId

) Temp on m. MemberId = temp. MemberId

Order by temp. MessageCount desc

</CommandText>

<Parameter Name = "datesent" Type = "datetime"/>

</Function>

2. open. edmx file design view. right-click the design view and select "add" transform function import. in the "Stored Procedure/function name" dialog box, select MembersWithTheMostMessages, and enter MembersWithTheMostMessages in the "Function Import Name" box ., select "entity" in "Returned content set" and select "Member" in the drop-down box. click OK ".

3. In the Listing 10-14 code, use the MembersWithTheMostMessages () method to call the custom function MembersWithTheMostMessages.

Listing 10-14.PassMembersWithTheMostMessages () call the custom function MembersWithTheMostMessages

Class Program

{

Static void Main (string [] args)

{

DateTime today = DateTime. Parse ("5/7/2013 ");

Using (var context = new EFRecipesEntities1005 ())

{

Var mem1 = new Member {Name = "Jill Robert tson "};

Var mem2 = new Member {Name = "Steven Rhodes "};

Mem1.Messages. Add (new Message

{

DateSent = today,

MessageBody = "Hello Jim ",

Subject = "Hello"

});

Mem1.Messages. Add (new Message

{

DateSent = today,

MessageBody = "Wonderful weather! ",

Subject = "Weather"

});

Mem1.Messages. Add (new Message

{

DateSent = today,

MessageBody = "Meet me for lunch ",

Subject = "Lunch plans"

});

Mem2.Messages. Add (new Message

{

DateSent = today,

MessageBody = "Going to class today? ",

Subject = "What's up? "

});

Context. Members. Add (mem1 );

Context. Members. Add (mem2 );

Context. SaveChanges ();

}

Using (var context = new EFRecipesEntities1005 ())

{

Console. WriteLine ("Members by message count for {0 }",

Today. tow.datestring ());

Var members = context. MembersWithTheMostMessages (today );

Foreach (var member in members)

{

Console. WriteLine ("Member: {0}", member. Name );

}

}

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

Console. ReadKey ();

}

}

The output result is as follows:

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

Members by message count for 5/7/2013

Member: Jill Robert tson

Member: Steven Rhodes

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

How does it work?

A user-defined function is different from a model-defined function (see Chapter 11) because the user-defined function is defined in the storage model. In this way, user-defined functions are stored in databases like traditional user-defined functions. If a stored procedure is like defining a "virtual" table in the storage model, a user-defined function is like a "virtual" stored procedure defined in the storage model.

Some EF teams call it "natural functions" and Microsoft's team uses "custom functions", so we also use this name.

The code in Listing 10-13 defines our custom function. We use the XML editor to put it into the. edmx file storage model node (<edmx: StorageModels>.

Note: If you use the "update model from database" Wizard to update the model, the wizard will overwrite the node. Therefore, before using the wizard, please be careful to save your modifications in the storage model.

Just like using stored procedures in the previous section, we use the "Function Import" Wizard to map user-defined functions to a method. It sets the method name and return type. In this example, the UDF returns the Member object set.

The code in Listing 10-14 shows how to call a custom function using the MembersWithTheMostMessages () method. This method is the same as calling a stored procedure.

Custom functions are applicable to the following situations:

L You do not have permission to create a stored procedure

L you want to separate the database and code for management. You can use user-defined functions instead of creating stored procedures in the database.

L when the parameters of stored procedures in the database are incompatible with your entities, you can use custom functions, use an abstraction layer to process the type conversion between stored procedure parameters and your object attributes.

Appendix: script file of the database used in the Creation example