Database Reference
In-Depth Information
Following is the output of the code in Listing 11-4:
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 It Works
From the definition of our
GetInvoices()
function in Listing 11-3, we see that it takes a collection of Invoices and
returns a collection of Invoices. On the CLR side, this translates to taking an
IQueryable<Invoice>
and returning an
IQueryable<Invoice>
.
In the eSQL expression, we use the
GetInvoices()
function in the
from
clause. We pass in the unfiltered
collection of Invoices and our
GetInvoices()
function returns the filtered collection. We further filter the
collection by date and the customer's city using a
where
clause. Then we use
CreateQuery<Invoice>()
to build
the
ObjectQuery<Invoice>
. In building the query, we pass in the parameter to filter by city and use the
Include()
method to include the related customers. Once we have the
ObjectQuery<Invoice>
, we iterate over the resulting
collection and print out the invoices that matched the two filters that we applied.
For the LINQ query, the story is a little more interesting. Here we build the expression using the
GetInvoices()
method in the
from
clause and filter the resulting collection by date and city, much like we did with the
eSQLexpression. However, to use our function in a LINQ query, we need to implement a CLR method that takes
an
IQueryable<Invoice
> and returns an
IQueryable<Invoice>
. Unlike the stub method in Recipe 11-1, in which
the model-defined function returned a scalar value, here we have to provide an implementation in the body of the
method. Creating this method is often referred to as
bootstrapping
.
Here are some rules for bootstrapping:
IQueryable<T>
.
•
Bootstrapping is required when a model-defined function returns an
IQueryable<T>
but does not take an
IQueryable<T>
, the
bootstrapping method must be implemented in a partial class of the ObjectContext.
The second rule comes about because we can't return an
IQueryable<T>
that has meaning in our ObjectContext
without starting with an
IQueryable<T>
. If we pass in an
IQueryable<T>
, then we can perform some operation in
our bootstrapping method that returns a related
IQueryable<T>
. However, we can't manufacture an
IQueryable<T>
outside of a partial class of our ObjectContext. In our example, we received an
IQueryable<T>
as a parameter, so we
are free to implement the bootstrapping code outside of a partial class of our ObjectContext.
In the implementation of our bootstrapping method, we get an instance of
IQueryProvider
from the
IQueryable<Invoice>
through the Provider property.
IQueryProvider.CreateQuery<Invoice>()
allows us to tack
onto the expression tree for the
IQueryable<T>
. Here we add in the call to the
GetInvoices()
function, passing in the
collection of invoices that we have.
•
When a function returns an
11-3. Returning a Computed Column from a Model-Defined
Function
Problem
You want to return a computed column from a model-defined function.
