Stored Procedures - Entity Framework 6 Recipes - page 349

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>

Open the .edmx file in the Designer. Right-click the design surface, and select Add ➤ Function
Import. In the dialog box, select the MembersWithTheMostMessages in the Stored Procedure
Name drop-down. Enter MembersWithTheMostMessages in the Function Import Name text
box. Finally, select Entities as the return type and choose Member as the entity type. Click OK.

2.

Follow the pattern in Listing 10-14 to use the MembersWithTheMostMessages() method,
which exposes the MembersWithTheMostMessages custom function.

3.

Listing 10-14. Using the MembersWithTheMostMessages Function via the
MembersWithTheMostMessages() method

DateTime today = DateTime.Parse("5/7/2013");
using (var context = new EF6RecipesContext())
{
var mem1 = new Member { Name = "Jill Robertson" };
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();
}