Export (0) Print
Expand All

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.

Starting with the .NET Framework 4.5, LINQ queries are cached automatically. However, you can still use compiled LINQ queries to reduce this cost in later executions and compiled queries can be more efficient than LINQ queries that are automatically cached. Note that LINQ to Entities queries that apply the Enumerable.Contains operator to in-memory collections are not automatically cached. Also parameterizing in-memory collections in compiled LINQ queries is not allowed.

The CompiledQuery class provides compilation and caching of queries for reuse. Conceptually, this class contains a CompiledQuery's 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 compiles once during only the first execution. The merge options set for the query at the time of the compilation cannot be changed later. Once the query is compiled you can only supply parameters of primitive type but you cannot replace parts of the query that would change the generated SQL. For more information, see Entity Framework Merge Options and Compiled Queries

The LINQ to Entities query expression that the CompiledQuery's 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 16 query parameters. If more than 16 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:


static readonly Func<AdventureWorksEntities, Decimal, IQueryable<SalesOrderHeader>> s_compiledQuery2 =
CompiledQuery.Compile<AdventureWorksEntities, Decimal, IQueryable<SalesOrderHeader>>(
(ctx, total) => from order in ctx.SalesOrderHeaders
where order.TotalDue >= total
select order);

static void CompiledQuery2()
{
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
Decimal totalDue = 200.00M;

IQueryable<SalesOrderHeader> orders = s_compiledQuery2.Invoke(context, 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:


static readonly Func<AdventureWorksEntities, ObjectQuery<SalesOrderHeader>> s_compiledQuery1 =
CompiledQuery.Compile<AdventureWorksEntities, ObjectQuery<SalesOrderHeader>>(
ctx => ctx.SalesOrderHeaders);

static void CompiledQuery1_MQ()
{

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
IQueryable<SalesOrderHeader> orders = s_compiledQuery1.Invoke(context);

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:


static readonly Func<AdventureWorksEntities, Decimal> s_compiledQuery3MQ = CompiledQuery.Compile<AdventureWorksEntities, Decimal>(
ctx => ctx.Products.Average(product => product.ListPrice));

static void CompiledQuery3_MQ()
{

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
Decimal averageProductPrice = s_compiledQuery3MQ.Invoke(context);

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:


static readonly Func<AdventureWorksEntities, string, Contact> s_compiledQuery4MQ =
CompiledQuery.Compile<AdventureWorksEntities, string, Contact>(
(ctx, name) => ctx.Contacts.First(contact => contact.EmailAddress.StartsWith(name)));

static void CompiledQuery4_MQ()
{
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
string contactName = "caroline";
Contact foundContact = s_compiledQuery4MQ.Invoke(context, 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:


static readonly Func<AdventureWorksEntities, DateTime, Decimal, IQueryable<SalesOrderHeader>> s_compiledQuery5 =
CompiledQuery.Compile<AdventureWorksEntities, DateTime, Decimal, IQueryable<SalesOrderHeader>>(
(ctx, orderDate, totalDue) => from product in ctx.SalesOrderHeaders
where product.OrderDate > orderDate
&& product.TotalDue < totalDue
orderby product.OrderDate
select product);

static void CompiledQuery5()
{
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
DateTime date = new DateTime(2003, 3, 8);
Decimal amountDue = 300.00M;

IQueryable<SalesOrderHeader> orders = s_compiledQuery5.Invoke(context, 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 CompiledQuery's Compile method and the type is defined in the query itself.


using (AdventureWorksEntities context = new AdventureWorksEntities())
{
var compiledQuery = CompiledQuery.Compile((AdventureWorksEntities ctx, DateTime orderDate) =>
from order in ctx.SalesOrderHeaders
where order.OrderDate > orderDate
select new {order.OrderDate, order.SalesOrderID, order.TotalDue});

DateTime date = new DateTime(2004, 3, 8);
var results = compiledQuery.Invoke(context, 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.salesorderheaders _ where sale.shipdate > mysearchparams.startdate _ and sale.shipdate < mysearchparams.enddate _ and sale.totaldue > mysearchparams.totaldue _ select sale) sub compiledquery7() using context 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(context, 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.salesorderheaders where sale.shipdate > myparams.startdate && sale.shipdate < myparams.enddate && sale.totaldue > myparams.totaldue select sale); static void compiledquery7() { using (adventureworksentities context = 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(context, 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:


struct MyParams
{
public DateTime startDate;
public DateTime endDate;
public decimal totalDue;
}
    

See Also




Build Date:

2012-10-01
Show:
© 2014 Microsoft