Functions - Entity Framework 6 Recipes - page 388

context.VisitorSummary(DateTime.Parse("2/16/2010"), 7)
select v;
foreach (var visitor in visitors)
{
Console.WriteLine("{0}, Total Reservations: {1}, Revenue: {2:C}",
visitor["Name"], visitor["TotalReservations"],
visitor["BusinessEarned"]);
}
}
}
}
partial class EFRecipesEntities
{
[EdmFunction("EFRecipesModel", "VisitorSummary")]
public IQueryable<DbDataRecord> VisitorSummary(DateTime StartDate, int Days)
{
return this.QueryProvider.CreateQuery<DbDataRecord>(
Expression.Call(
Expression.Constant(this),
(MethodInfo)MethodInfo.GetCurrentMethod(),
new Expression[] { Expression.Constant(StartDate),
Expression.Constant(Days) }
));
}
}
The output from the code in Listing 11-10 is as follows:
Using eSQL...
Alex Stevens, Total Reservations: 2, Revenue: $189.98
Joan Hills, Total Reservations: 2, Revenue: $189.98
Using LINQ...
Alex Stevens, Total Reservations: 2, Revenue: $189.98
Joan Hills, Total Reservations: 2, Revenue: $189.98
How It Works
In Listing 11-9, for the definition of the VisitorSummary() function, we group the results by visitor, which is the
navigation property exposed on the entity. To get the total count of reservations for each visitor, we use the eSQL
Count() function. To get the total revenue, we use the Sum() function.
In the function, we shape the results as a collection of rows of three values: Name, TotalReservations, and
BusinessEarned. Here we use the <CollectionType> and <RowType> tags to indicate the return type. In CLR terms,
this is a collection of DbDataRecords.
To use the function in a LINQ query, we create a CLR method that returns IQueryable<DbDataRecord> . As in
the previous recipes, we decorated the method with the EdmFunction() attribute. However, because we are returning
an IQueryable<T> , we need to implement the body of the method to include the function call in the expression tree.
Furthermore, because we need access to the QueryProvider in our ObjectContext to return an IQueryable<T> ,
we need to implement this method inside the EFRecipesEntities class.