LINQ to Entities: Compiled Queries

Last Friday I blogged about making local variables behave consistently in LINQ to Entities queries. And the solution I recommended was compiled queries. But just today I realized that I haven’t blogged about compiled queries in general yet.

 

The main purpose of compiled queries to improve performance. While regular LINQ to Entities queries are re-compiled before every execution, compiled queries are compiled only once (subject to Query Plan Cache availability) and cached. Subsequent executions pull the query plan from the Query Plan Cache. Additionally, compiled queries improve the experience with using local variables and parameters.

 

The “query” part of compiled queries is a regular LINQ to entities query with explicit parameters. A compiled query has at least one parameter – an ObjectContext. That way a query instance can be compiled before the ObjectContext instance is constructed. That also means that a single compiled query may be executed against different ObjectContext instances as long as those two instance have loaded the same entity model.

 

Usage of parameters is explicit with compiled queries - a compiled query may still reference local variables, but those values are taken as constants.

 

Following is an example of a compiled query that retrieves the products whose names start with a user-provided text string in the Northwind model:

 

        var products = CompiledQuery.Compile((Northwind northwind, string startsWith) =>

                                                    from product in northwind.Products

                                                    where product.ProductName.StartsWith(startsWith)

                                                    orderby product.ProductName

                                                    select product);

            using (Northwind northwind = new Northwind(Program.NorthwindConnectionString))

            {

                foreach (var product in products(northwind, "C"))

        {

                    Console.WriteLine(product.ProductName);

                }

            }