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

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

11-2. Use the "model definition" function to filter entity sets

Problem

You want to create a "model definition" function to filter an object set.

Solution

Assume that we have a Customer and bill Invoice model, as shown in Figure 11-2.

 

Figure 11-2.Customer and Invoice in a model

We want to create a "model definition" function to obtain the invoice set and filter out the invoice whose total number is higher than $300. for more fun, let's use a "model definition" function in a query. This query further filters the invoice created after 5/1/2013. of course, what we want is all the invoice that the customer meets the preceding conditions.

Follow these steps:

1. In the solution, right-click the. edmx file and open the idea XML editor.

2. In the. edmx file, insert the code shown in Listing 11-3 under the <Schema> tag in the conceptual models cell.

Listing 11-3.The GetInvoices () Model-Defined Function

<Function Name = "GetInvoices" ReturnType = "Collection (EFRecipesModel1102.Invoice)">

<Parameter Name = "invoices" Type = "Collection (EFRecipesModel1102.Invoice)">

</Parameter>

<DefiningExpression>

Select VALUE I from invoices as I where I. Amount & gt; 300 M

</DefiningExpression>

</Function>

3. insert and query the Model Code, as shown in Listing 11-4:

Listing 11-4.The GetInvoices () method ("model definition" function) is used to query models in eSQL and LINQ modes.

Class Program

{

Static void Main (string [] args)

{

RunExample ();

}

Static void RunExample ()

{

Using (var context = new EFRecipesEntities1102 ())

{

DateTime d1 = DateTime. Parse ("8/8/2013 ");

DateTime d2 = DateTime. Parse ("8/12/2012 ");

Var c1 = new Customer {Name = "Jill Robinson", City = "Dallas "};

Var c2 = new Customer {Name = "Jerry Jones", City = "Denver "};

Var c3 = new Customer {Name = "Janis Brady", City = "Dallas "};

Var c4 = new Customer {Name = "Steve Foster", City = "Dallas "};

Context. Invoices. Add (new Invoice

{

Amount = 302.99 M,

Description = "New Tires ",

Date = d1,

Customer = c1

});

Context. Invoices. Add (new Invoice

{

Amount = 430.39 M,

Description = "Brakes and Shocks ",

Date = d1,

Customer = c2

});

Context. Invoices. Add (new Invoice

{

Amount = 102.28 M,

Description = "Wheel Alignment ",

Date = d1,

Customer = c3

});

Context. Invoices. Add (new Invoice

{

Amount = 629.82 M,

Description = "A/C Repair ",

Date = d2,

Customer = c4

});

Context. SaveChanges ();

}

 

Using (var context = new EFRecipesEntities1102 ())

{

Console. WriteLine ("Using eSQL query ...");

String SQL = @ "Select value I from

EFRecipesModel1102.GetInvoices (EFRecipesEntities1102.Invoices) as I

Where I. Date> DATETIME '2017-05-1'

And I. Customer. City = @ City ";

Var objectContext = (context as IObjectContextAdapter). ObjectContext;

Var invoices = objectContext. CreateQuery <Invoice> (SQL,

New ObjectParameter ("City", "Dallas"). Include ("Customer ");

Foreach (var invoice in invoices)

{

Console. WriteLine ("Customer: {0} \ tInvoice for: {1}, Amount: {2 }",

Invoice. Customer. Name, invoice. Description, invoice. Amount );

}

}

Using (var context = new EFRecipesEntities1102 ())

{

Console. WriteLine ();

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

DateTime date = DateTime. Parse ("5/1/2013 ");

Var invoices = from invoice in

MyFunctions. GetInvoices (context. Invoices)

Where invoice. Date> date

Where invoice. Customer. City = "Dallas"

Select invoice;

Foreach (var invoice in (DbQuery <Invoice>) invoices)

. Include ("Customer "))

{

Console. WriteLine ("Customer: {0}, Invoice for: {1}, Amount: {2 }",

Invoice. Customer. Name, invoice. Description, invoice. Amount );

}

}

Console. WriteLine ("\ nPress any key to exit ...");

Console. ReadKey ();

}

}

Public class MyFunctions

{

[EdmFunction ("EFRecipesModel1102", "GetInvoices")]

Public static IQueryable <Invoice> GetInvoices (IQueryable <Invoice> invoices)

{

Return invoices. Provider. CreateQuery <Invoice> (

Expression. Call (MethodInfo) MethodInfo. GetCurrentMethod (),

Expression. Constant (invoices, typeof (IQueryable <Invoice> ))));

}

 

}

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

Using eSQL for the query...

Customer: Jill Robinson Invoice for: New Tires, Amount: 302.99

 

Using LINQ for the query...

Customer: Jill Robinson, Invoice for: New Tires, Amount: 302.99

 

How does it work?

From the GetInvoices () function defined in Listing 11-3, we can see that it accepts an Invoice set and returns an Invoice set. during running, it is interpreted as accepting an IQueryable <Invoice> and returning an IQueryable <Invoice>.

In eSQL expressions, we use the GetInvoices () function in the from clause. we pass in the unfiltered Invoice set, and then our GetInvoices () function returns a filtered Invoice set. we use the Where clause to filter this set based on the date and the Customer's cities. then we use CreateQuery <Invoice> () to create the ObjectQuery <Invoice> type. in the created query, we pass a parameter (the city where the customer is located) to filter and use the Include () method to Include the associated customer. once ObjectQuery <Invoice> is available, we traverse the result set and print the qualified invoice.

It is interesting to query using LINQ. We put the GetInvoices () method in the Form clause of the expression, and then filter the result based on the date and city (this is similar to the eSQL expression ). however, to use our function in a LINQ query, we need to implement a runtime method that accepts an IQueryable <Invoice> and returns an IQueryable <Invoice>.

Unlike method stubs that return a scalar value using the "model definition" function in section 11-1, we must implement the method here. To create this method, refer to the Bootstrap program.

The following are the rules of the Bootstrap program:

If the "model definition" function returns an IQueryable <T>, the Bootstrap program must be used.

When a function returns an IQueryable <T> but does not accept an IQueryable <T>, the bootstrap method must be implemented as part of the ObjectContext class.

Because of the second rule, we cannot return an IQueryable even if the ObjectContext does not start with an IQueryable <T>. but we can pass in an IQueryable <T>, and then execute some operations in our bootstrap method to make it return a related IQueryable <T>. although we cannot manually create an IQueryable <T> outside the ObjectContext class, in our example, after receiving an IQueryable <T> as a parameter, we can implement the guiding code for our ObjectContext outside the class.

In our implementation of the bootstrap method, we get an IQueryable <Invoice>. you can get an IqueryProvider instance through its Provider attribute, IQueryProvider. createQuery <Invoice> () allows us to add IQueryable <T> to the expression tree.

Of course, this method also uses the relevant "functions" feature to modify and pass in an invoice set.

 

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