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<T> 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<T1, T2, T3, T4, TResult>. 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.

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:

[!CODE [DP L2E Conceptual Examples#CompiledQuery2](../CodeSnippet/VS_Snippets_Data/DP L2E Conceptual Examples#compiledquery2)]

The following example compiles and then invokes a query that returns an ObjectQuery<T> instance:

[!CODE [DP L2E Conceptual Examples#CompiledQuery1_MQ](../CodeSnippet/VS_Snippets_Data/DP L2E Conceptual Examples#compiledquery1_mq)]

The following example compiles and then invokes a query that returns the average of the product list prices as a Decimal value:

[!CODE [DP L2E Conceptual Examples#CompiledQuery3_MQ](../CodeSnippet/VS_Snippets_Data/DP L2E Conceptual Examples#compiledquery3_mq)]

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:

[!CODE [DP L2E Conceptual Examples#CompiledQuery4_MQ](../CodeSnippet/VS_Snippets_Data/DP L2E Conceptual Examples#compiledquery4_mq)]

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:

[!CODE [DP L2E Conceptual Examples#CompiledQuery5](../CodeSnippet/VS_Snippets_Data/DP L2E Conceptual Examples#compiledquery5)]

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.

[!CODE [DP L2E Conceptual Examples#CompiledQuery6](../CodeSnippet/VS_Snippets_Data/DP L2E Conceptual Examples#compiledquery6)]

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.

[!CODE [DP L2E Conceptual Examples#CompiledQuery7](../CodeSnippet/VS_Snippets_Data/DP L2E Conceptual Examples#compiledquery7)]

The structure that defines the query parameters:

[!CODE [DP L2E Conceptual Examples#MyParamsStruct](../CodeSnippet/VS_Snippets_Data/DP L2E Conceptual Examples#myparamsstruct)]

ADO.NET Entity Framework
LINQ to Entities
Entity Framework Merge Options and Compiled Queries

Show: