Entity Framework 6 Recipes 2nd Edition (11-5), recipes11-5

Entity Framework 6 Recipes 2nd Edition (11-5), recipes11-5

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

Problem

Create a "model definition" function that returns an anonymous type

Solution

Assume that there is a model of a hotel for a Visitor reservation, as shown in Figure 11-5.

 

Figure 11-5.A model for hotel reservations

Returns the number of reservations and total income for each visitor's room. because this information is required in many places, you want to create a "model definition" function, accept a query parameter, and return a set of anonymous types that contain the aggregate information of visitors:

2. Insert the code from Listing 11-9 to the conceptual model of the. edmx file under the <Schema> tag, so that we can 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. insert and query the model. The code is 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 Steven s "};

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

Var r1 = new Reservation

{

Cost = 79.99 M,

Hotel = hotel,

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

Visitor = v1

};

Var r2 = new Reservation

{

Cost = 99.99 M,

Hotel = hotel,

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

Visitor = v2

};

Var r3 = new Reservation

{

Cost = 109.99 M,

Hotel = hotel,

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

Visitor = v1

};

Var r4 = new Reservation

{

Cost = 89.99 M,

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 '2017-02-16 ', 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 ...");

// Note: In my EF6.1.1.3, this query will be 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 above Listing 11-10 code output is as follows:

Using eSQL...

Alex Steven s, Total Reservations: 2, Revenue: $189.98

Joan Hills, Total Reservations: 2, Revenue: $189.98

Using LINQ...

Alex Steven s, 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 group by object navigation attribute visitor. We use the eSQL Count () function to calculate the number of reserved rooms for each visitor, sum () is used to aggregate the rent paid by each visitor.

In this function, we organize the result into: Name, TotalReservations, and BusinessEarned. the <CollectionType> and <RowType> labels are used to specify the return type. when running, use a set containing DbDataRecords

To enable this function to be used in a LINQ query, we have created a runtime method (returned IQueryable <DbDataRecord> .). as in the previous section, we use the EdmFunction () feature to modify this method .. because you need to return an IQueryable <T>, we need to include a 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 an IQueryable <T>, so we need to implement this method in the EFRecipesEntities class.

 

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