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

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

11-6. Return a complex type from a "model definition" Function

Problem

To return a complex type from a "model definition" Function

Solution

Suppose we have a patient (patient) and their visitor (visit) model, as shown in Figure 11-6.

 

Figure 11-6.A model for patient visits

We want to create a "model definition" function and return a summary, including the patient name, the number of visitors to the patient, and the patient's cumulative bill. in addition, we only filter out patients over 40 years of age:

1. Right-click the model design view to create a complex sequence.

2. In the model browser, right-click the new complex type, rename it as VisitSummary, and add the following attributes to the complex property ::

A. Name: String, cannot be null

B. TotalVisits: Int32. The value cannot be null.

C. TotalCost: Decimal, cannot be null

3. In Solution Explorer, right-click the. edmx file and open the method development XML editor.

4. insert it under the <Schema> label of the conceptual model conceptual models of the. edmx FileListing 11-11Code shownIn this way, the function is defined.

Listing 11-11.The GetVisitSummary () Model-Defined Function

 

<Function Name = "GetVisitSummary" ReturnType = "Collection (EFRecipesModel. VisitSummary)">

<DefiningExpression>

Select VALUE EFRecipesModel. VisitSummary (pv. Patient. Name,

Count (pv. VisitId), Sum (pv. Cost ))

From EFRecipesEntities. PatientVisits as pv

Group by pv. Patient. Name

</DefiningExpression>

</Function>

5. Use the code shown in Listing 11-12. To insert and query this model:

Listing 11-12.Using eSQL and LINQ with the VisitSummary () Function to Query the Model

Class Program

{

Static void Main (string [] args)

{

RunExample ();

}

Static void RunExample ()

{

Using (var context = new EFRecipesEntities ())

{

String hospital = "Oakland General ";

Var p1 = new Patient {Name = "Robin Rosen", Age = 41 };

Var p2 = new Patient {Name = "Alex Jones", Age = 39 };

Var p3 = new Patient {Name = "Susan Kirby", Age = 54 };

Var v1 = new PatientVisit

{

Cost = 98.38 M,

Hospital = hospital,

Patient = p1

};

Var v2 = new PatientVisit

{

Cost = 1122.98 M,

Hospital = hospital,

Patient = p1

};

Var v3 = new PatientVisit

{

Cost = 2292.72 M,

Hospital = hospital,

Patient = p2

};

Var v4 = new PatientVisit

{

Cost = 1145.73 M,

Hospital = hospital,

Patient = p3

};

Var v5 = new PatientVisit

{

Cost = 2891.07 M,

Hospital = hospital,

Patient = p3

};

Context. Patients. Add (p1 );

Context. Patients. Add (p2 );

Context. Patients. Add (p3 );

Context. SaveChanges ();

}

Using (var context = new EFRecipesEntities ())

{

Console. WriteLine ("Query using eSQL ...");

Var esql = @ "Select value ps from EFRecipesEntities. Patients

As p join EFRecipesModel. GetVisitSummary ()

As ps on p. Name = ps. Name where p. Age> 40 ";

Var objectContext = (context as IObjectContextAdapter). ObjectContext;

Var patients = objectContext. CreateQuery <VisitSummary> (esql );

Foreach (var patient in patients)

{

Console. WriteLine ("{0}, Visits: {1}, Total Bill: {2 }",

Patient. Name, patient. TotalVisits. ToString (),

Patient. TotalCost. ToString ("C "));

}

}

 

Using (var context = new EFRecipesEntities ())

{

Console. WriteLine ();

Console. WriteLine ("Query using LINQ ...");

// Note: The exception is the same as that in 11-5.

Var patients = from p in context. Patients

Join ps in context. GetVisitSummary () on p. Name equals

Ps. Name

Where p. Age> = 40

Select ps;

Foreach (var patient in patients)

{

Console. WriteLine ("{0}, Visits: {1}, Total Bill: {2 }",

Patient. Name, patient. TotalVisits. ToString (),

Patient. TotalCost. ToString ("C "));

}

}

}

}

Partial class EFRecipesEntities

{

[EdmFunction ("EFRecipesModel", "GetVisitSummary")]

Public IQueryable <VisitSummary> GetVisitSummary ()

{

Var objectContext = (this as IObjectContextAdapter). ObjectContext;

Return objectContext. CreateQuery <VisitSummary> (

Expression. Call (Expression. Constant (this ),

(MethodInfo) MethodInfo. GetCurrentMethod (). ToString ());

}

}

The output result of the Listing 11-12 code is as follows:

Query using eSQL...

Robin Rosen, Visits: 2, Total Bill: $1,221.36

Susan Kirby, Visits: 2, Total Bill: $4,036.80

Query using LINQ...

Robin Rosen, Visits: 2, Total Bill: $1,221.36

Susan Kirby, Visits: 2, Total Bill: $4,036.80

How does it work?

We first add a complex type to the model, and then create the GetVisitSummary () function such as Listing 11-11. It can return a set containing the new complex type. note: The order of parameters accepted by constructor of complex types must be the same as that when we define its attributes. you may need to check. edmx file to ensure that the designer is consistent with the order we add.

Because our function returns IQueryable <VisitSummary>, we need to implement the bootstrap code. Similarly, because we need to access the QueryProvider of ObjectContext, we need to implement the current runtime method in the EFRecipesEntities class.

If we use this function in a LINQ query, you may need to make this method return IQueryable <DbDataRecord> to the anonymous type. although this set cannot be further filtered, a set containing complex types can be filtered.

 

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