Compiled Queries (LINQ to Entities)
When you have an application that executes structurally similar queries many times in the Entity Framework, you can frequently increase performance by compiling the query one time and executing it several times with different parameters. For example, an application might have to retrieve all the customers in a particular city; the city is specified at runtime by the user in a form. LINQ to Entities supports using compiled queries for this purpose.
The CompiledQuery class provides compilation and caching of queries for reuse. Conceptually, this class contains a Compile method with several overloads. Call the Compile method to create a new delegate to represent the compiled query. The Compile methods, provided with a ObjectContext and parameter values, return a delegate that produces some result (such as an IQueryable instance). The query is translated and cached when the delegate is invoked for the first time.
The LINQ to Entities query expression that the Compile method compiles is represented by one of the generic Func delegates, such as Func. At most, the query expression can encapsulate an ObjectContext parameter, a return parameter, and three query parameters. If more than three query parameters are required, you can create a structure whose properties represent query parameters. You can then use the properties on the structure in the query expression after you set the properties.
Example
The following example compiles and then invokes a query that accepts a Decimal input parameter and returns a sequence of orders where the total due is greater than or equal to $200.00:
ReadOnly s_compQuery2 As Func(Of AdventureWorksEntities, Decimal, IQueryable(Of SalesOrderHeader)) = _
CompiledQuery.Compile(Of AdventureWorksEntities, Decimal, IQueryable(Of SalesOrderHeader))( _
Function(ctx As AdventureWorksEntities, total As Decimal) _
From order In ctx.SalesOrderHeader _
Where (order.TotalDue >= total) _
Select order)
Sub CompiledQuery2()
Using AWEntities As New AdventureWorksEntities()
Dim totalDue As Decimal = 200.0
Dim orders As IQueryable(Of SalesOrderHeader) = s_compQuery2.Invoke(AWEntities, totalDue)
For Each order In orders
Console.WriteLine("ID: {0} Order date: {1} Total due: {2}", _
order.SalesOrderID, _
order.OrderDate, _
order.TotalDue)
Next
End Using
End Sub
static readonly Func<AdventureWorksEntities, Decimal, IQueryable<SalesOrderHeader>> s_compiledQuery2 =
CompiledQuery.Compile<AdventureWorksEntities, Decimal, IQueryable<SalesOrderHeader>>(
(ctx, total) => from order in ctx.SalesOrderHeader
where order.TotalDue >= total
select order);
static void CompiledQuery2()
{
using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
Decimal totalDue = 200.00M;
IQueryable<SalesOrderHeader> orders = s_compiledQuery2.Invoke(AWEntities, totalDue);
foreach (SalesOrderHeader order in orders)
{
Console.WriteLine("ID: {0} Order date: {1} Total due: {2}",
order.SalesOrderID,
order.OrderDate,
order.TotalDue);
}
}
}
Example
The following example compiles and then invokes a query that returns an ObjectQuery instance:
ReadOnly s_compQuery1 As Func(Of AdventureWorksEntities, ObjectQuery(Of SalesOrderHeader)) = _
CompiledQuery.Compile(Of AdventureWorksEntities, ObjectQuery(Of SalesOrderHeader))( _
Function(ctx) ctx.SalesOrderHeader)
Sub CompiledQuery1_MQ()
Using AWEntities As New AdventureWorksEntities()
Dim orders As ObjectQuery(Of SalesOrderHeader) = s_compQuery1.Invoke(AWEntities)
For Each order In orders
Console.WriteLine(order.SalesOrderID)
Next
End Using
End Sub
static readonly Func<AdventureWorksEntities, ObjectQuery<SalesOrderHeader>> s_compiledQuery1 =
CompiledQuery.Compile<AdventureWorksEntities, ObjectQuery<SalesOrderHeader>>(
ctx => ctx.SalesOrderHeader);
static void CompiledQuery1_MQ()
{
using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
IQueryable<SalesOrderHeader> orders = s_compiledQuery1.Invoke(AWEntities);
foreach (SalesOrderHeader order in orders)
Console.WriteLine(order.SalesOrderID);
}
}
Example
The following example compiles and then invokes a query that returns the average of the product list prices as a Decimal value:
Using AWEntities As New AdventureWorksEntities()
Dim compQuery = CompiledQuery.Compile(Of AdventureWorksEntities, Decimal)( _
Function(ctx) ctx.Product.Average(Function(Product) Product.ListPrice))
Dim averageProductPrice As Decimal = compQuery.Invoke(AWEntities)
Console.WriteLine("The average of the product list prices is $: {0}", averageProductPrice)
End Using
static readonly Func<AdventureWorksEntities, Decimal> s_compiledQuery3MQ = CompiledQuery.Compile<AdventureWorksEntities, Decimal>(
ctx => ctx.Product.Average(product => product.ListPrice));
static void CompiledQuery3_MQ()
{
using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
Decimal averageProductPrice = s_compiledQuery3MQ.Invoke(AWEntities);
Console.WriteLine("The average of the product list prices is $: {0}", averageProductPrice);
}
}
Example
The following example compiles and then invokes a query that accepts a String input parameter and then returns a Contact whose e-mail address starts with the specified string:
Using AWEntities As New AdventureWorksEntities()
Dim compQuery = CompiledQuery.Compile(Of AdventureWorksEntities, String, Contact)( _
Function(ctx, name) ctx.Contact.First(Function(contact) contact.EmailAddress.StartsWith(name)))
Dim contactName As String = "caroline"
Dim foundContact As Contact = compQuery.Invoke(AWEntities, contactName)
Console.WriteLine("An email address starting with 'caroline': {0}", _
foundContact.EmailAddress)
End Using
static readonly Func<AdventureWorksEntities, string, Contact> s_compiledQuery4MQ =
CompiledQuery.Compile<AdventureWorksEntities, string, Contact>(
(ctx, name) => ctx.Contact.First(contact => contact.EmailAddress.StartsWith(name)));
static void CompiledQuery4_MQ()
{
using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
string contactName = "caroline";
Contact foundContact = s_compiledQuery4MQ.Invoke(AWEntities, contactName);
Console.WriteLine("An email address starting with 'caroline': {0}",
foundContact.EmailAddress);
}
}
Example
The following example compiles and then invokes a query that accepts DateTime and Decimal input parameters and returns a sequence of orders where the order date is later than March 8, 2003, and the total due is less than $300.00:
ReadOnly s_compQuery5 = _
CompiledQuery.Compile(Of AdventureWorksEntities, DateTime, Decimal, IQueryable(Of SalesOrderHeader))( _
Function(ctx, orderDate, totalDue) From product In ctx.SalesOrderHeader _
Where Product.OrderDate > orderDate _
And Product.TotalDue < totalDue _
Order By Product.OrderDate _
Select Product)
Sub CompiledQuery5()
Using AWEntities As New AdventureWorksEntities()
Dim orderedAfterDate As DateTime = New DateTime(2003, 3, 8)
Dim amountDue As Decimal = 300.0
Dim orders As IQueryable(Of SalesOrderHeader) = _
s_compQuery5.Invoke(AWEntities, orderedAfterDate, amountDue)
For Each order In orders
Console.WriteLine("ID: {0} Order date: {1} Total due: {2}", _
order.SalesOrderID, order.OrderDate, order.TotalDue)
Next
End Using
End Sub
static readonly Func<AdventureWorksEntities, DateTime, Decimal, IQueryable<SalesOrderHeader>> s_compiledQuery5 =
CompiledQuery.Compile<AdventureWorksEntities, DateTime, Decimal, IQueryable<SalesOrderHeader>>(
(ctx, orderDate, totalDue) => from product in ctx.SalesOrderHeader
where product.OrderDate > orderDate
&& product.TotalDue < totalDue
orderby product.OrderDate
select product);
static void CompiledQuery5()
{
using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
DateTime date = new DateTime(2003, 3, 8);
Decimal amountDue = 300.00M;
IQueryable<SalesOrderHeader> orders = s_compiledQuery5.Invoke(AWEntities, date, amountDue);
foreach (SalesOrderHeader order in orders)
{
Console.WriteLine("ID: {0} Order date: {1} Total due: {2}", order.SalesOrderID, order.OrderDate, order.TotalDue);
}
}
}
Example
The following example compiles and then invokes a query that accepts a DateTime input parameter and returns a sequence of orders where the order date is later than March 8, 2004. This query returns the order information as a sequence of anonymous types. Anonymous types are inferred by the compiler, so you cannot specify type parameters in the Compile method and the type is defined in the query itself.
Using AWEntities As New AdventureWorksEntities()
Dim compQuery = CompiledQuery.Compile( _
Function(ctx As AdventureWorksEntities, orderDate As DateTime) _
From order In ctx.SalesOrderHeader _
Where order.OrderDate > orderDate _
Select New With {order.OrderDate, order.SalesOrderID, order.TotalDue})
Dim orderedAfterDate As DateTime = New DateTime(2004, 3, 8)
Dim orders = compQuery.Invoke(AWEntities, orderedAfterDate)
For Each order In orders
Console.WriteLine("ID: {0} Order date: {1} Total due: {2}", _
order.SalesOrderID, order.OrderDate, order.TotalDue)
Next
End Using
using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
var compiledQuery = CompiledQuery.Compile((AdventureWorksEntities ctx, DateTime orderDate) =>
from order in ctx.SalesOrderHeader
where order.OrderDate > orderDate
select new {order.OrderDate, order.SalesOrderID, order.TotalDue});
DateTime date = new DateTime(2004, 3, 8);
var results = compiledQuery.Invoke(AWEntities, date);
foreach (var order in results)
{
Console.WriteLine("ID: {0} Order date: {1} Total due: {2}", order.SalesOrderID, order.OrderDate, order.TotalDue);
}
}
Example
The following example compiles and then invokes a query that accepts a user-defined structure input parameter and returns a sequence of orders. The structure defines start date, end date, and total due query parameters, and the query returns orders shipped between March 3 and March 8, 2003 with a total due greater than $700.00.
ReadOnly s_compQuery = CompiledQuery.Compile(Of AdventureWorksEntities, MyParams, IQueryable(Of SalesOrderHeader))( _
Function(ctx, mySearchParams) _
From sale In ctx.SalesOrderHeader _
Where sale.ShipDate > mySearchParams.startDate _
And sale.ShipDate < mySearchParams.endDate _
And sale.TotalDue > mySearchParams.totalDue _
Select sale)
Sub CompiledQuery7()
Using AWEntities As New AdventureWorksEntities()
Dim myParams As MyParams = New MyParams()
myParams.startDate = New DateTime(2003, 3, 3)
myParams.endDate = New DateTime(2003, 3, 8)
myParams.totalDue = 700.0
Dim sales = s_compQuery.Invoke(AWEntities, myParams)
For Each sale In sales
Console.WriteLine("ID: {0}", sale.SalesOrderID)
Console.WriteLine("Ship date: {0}", sale.ShipDate)
Console.WriteLine("Total due: {0}", sale.TotalDue)
Next
End Using
End Sub
static Func<AdventureWorksEntities, MyParams, IQueryable<SalesOrderHeader>> s_compiledQuery =
CompiledQuery.Compile<AdventureWorksEntities, MyParams, IQueryable<SalesOrderHeader>>(
(ctx, myparams) => from sale in ctx.SalesOrderHeader
where sale.ShipDate > myparams.startDate && sale.ShipDate < myparams.endDate
&& sale.TotalDue > myparams.totalDue
select sale);
static void CompiledQuery7()
{
using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
MyParams myParams = new MyParams();
myParams.startDate = new DateTime(2003, 3, 3);
myParams.endDate = new DateTime(2003, 3, 8);
myParams.totalDue = 700.00M;
IQueryable<SalesOrderHeader> sales = s_compiledQuery.Invoke(AWEntities, myParams);
foreach (SalesOrderHeader sale in sales)
{
Console.WriteLine("ID: {0}", sale.SalesOrderID);
Console.WriteLine("Ship date: {0}", sale.ShipDate);
Console.WriteLine("Total due: {0}", sale.TotalDue);
}
}
}
The structure that defines the query parameters:
Public Structure MyParams
Public startDate As DateTime
Public endDate As DateTime
Public totalDue As Decimal
End Structure
struct MyParams
{
public DateTime startDate;
public DateTime endDate;
public decimal totalDue;
}