Entity Framework 6 Recipes 2nd Edition (11-5)---return an anonymous type from the model definition function

11-5. Return an anonymous type from the model definition function

Problem

Want to create a "model definition" function that returns an anonymous type

Solution Solutions

Suppose you have a model for a guest (Visitor) reservation (reservation) room (hotel), as shown in Figure 11-5.

Figure 11-5. A model for hotel reservations

Want to return the number of bookings per guest room and the total revenue generated. Because this information is needed in many places, you want to create a "model definition" function, accept a query parameter, and return a collection of anonymous types that contain visitor totals information:

< p="">

2. Insert the code from listing 11-9 into the conceptual model of the. edmx file <Schema> tag, so that we define the function.

Listing 11-9. The Visitorsummary () model-defined Function

<function name= "Visitorsummary" >

<parameter name= "StartDate" type= "Edm.datetime"/>

<parameter name= "Days" type= "Edm.int32"/>

<ReturnType>

<CollectionType>

<RowType>

<property name= "Name" type= "edm.string"/>

<property name= "totalreservations" type= "Edm.int32"/>

<property name= "businessearned" type= "Edm.decimal"/>

</RowType>

</CollectionType>

</ReturnType>

<DefiningExpression>

Select

R.visitor.name,

COUNT (R.reservationid) as Totalreservations,

SUM (r.cost) as businessearned

From Efrecipesentities.reservations as R

where r.reservationdate between StartDate and

AddDays (Startdate,days)

GROUP BY R.visitor.name

</DefiningExpression>

</Function>

3. Next insert and query model, code as shown in Listing 11-10:.

Listing 11-10. Querying the Model Using the Vistorysummary () model-defined Function

Class Program

{

static void Main (string[] args)

{

Runexample ();

}

static void Runexample ()

{

using (var context = new Efrecipesentities ())

{

var hotel = new hotel {Name = "Five Seasons Resort"};

var v1 = new Visitor {Name = "Alex Stevens"};

var v2 = new Visitor {Name = "Joan Hills"};

var r1 = new Reservation

{

Cost = 79.99M,

hotel = Hotel,

Reservationdate = DateTime.Parse ("2/19/2010"),

Visitor = V1

};

var r2 = new Reservation

{

Cost = 99.99M,

hotel = Hotel,

Reservationdate = DateTime.Parse ("2/17/2010"),

Visitor = V2

};

var r3 = new Reservation

{

Cost = 109.99M,

hotel = Hotel,

Reservationdate = DateTime.Parse ("2/18/2010"),

Visitor = V1

};

var r4 = new Reservation

{

Cost = 89.99M,

hotel = Hotel,

Reservationdate = DateTime.Parse ("2/17/2010"),

Visitor = V2

};

Context. Hotels.add (hotel);

Context. SaveChanges ();

}

using (var context = new Efrecipesentities ())

{

Console.WriteLine ("Using ESQL ...");

var esql = @ "Select Value V from

Efrecipesmodel.visitorsummary (DATETIME ' 2010-02-16 00:00 ', 7) as V ";

var ObjectContext = (context as Iobjectcontextadapter). ObjectContext;

var visitors = objectcontext.createquery<dbdatarecord> (esql);

foreach (Var visitor in visitors)

{

Console.WriteLine ("{0}, total reservations: {1}, Revenue: {2:c}",

visitor["Name"], visitor["Totalreservations"],

visitor["businessearned"]);

}

}

using (var context = new Efrecipesentities ())

{

Console.WriteLine ();

Console.WriteLine ("Using LINQ ...");

In my EF6.1.1.3, this query will appear abnormal

var visitors = from V in

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)

{

var ObjectContext = (this as iobjectcontextadapter). ObjectContext;

Return objectcontext.createquery<dbdatarecord> (

Expression.call (Expression.constant (this),

(MethodInfo) Methodinfo.getcurrentmethod (),

New expression[] {expression.constant (startdate),

Expression.constant (Days)}

). ToString ());

}

}

The code output for listing 11-10 above 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 does it work?

In Listing 11-9, in the visitorsummary () function definition, we grouped by the entity's navigation property visitor, we used the ESQL count () function to calculate the number of bookings per visitor, and sum () to total the rent paid by each visitor.

In this function, we organize the results into: Name, totalreservations, and businessearned. Here we use the <CollectionType> and <RowType> tags to indicate the return type. In run time, use a collection containing dbdatarecords

In order for the function to be used in a LINQ query, we created a run-time method (return iqueryable<dbdatarecord>). As in the previous section, we decorated the method with the Edmfunction () feature. Because we need to return a iqueryable<t>, we need to include the function call in this method body so that it can be used in the query expression tree.

In addition, we need to access Queryprovider in ObjectContext to return a iqueryable<t>, so we need to implement this method in the Efrecipesentities class.

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

Entity Framework 6 Recipes 2nd Edition (11-5)---return an anonymous type from the model definition function