CompiledQuery in Entity Framework

CompiledQuery in Entity Framework

There are times
when we want
to make optimizations
on some piece of
code. If we want to
reduce the cost of
executing a query in Entity Framework we can use a CompiledQuery
to the rescue. Yesterday I used added a compiled query to a code
base which was executing multiple times. This reduced the execution
time of the repeating queries. In the post I’ll explain what are
CompiledQueries and how to use them.

CompiledQuery

CompiledQueries are a delegate which store a compiled LINQ query that we
have built in advance. When we use them we can reduce the cost of
executing a LINQ query. This can be very helpful when we have a query that
we execute multiple times. Pay attention that the compilation of a query
also cost so don’t use CompliedQueries for every query you build only for
the queries that you want to run a lot.

Creating a CompliedQuery

When we want to use a CompiledQuery we will first create a static member
which will hold the delegate that will be created from the CompiledQuery.
The reason for the static member is because CompliedQuery is always
used with a context and we don’t want to lose it when we dispose the
context. The following code is showing how to declare a CompiledQuery:

private static Func<SchoolEntities, decimal, IQueryable<Department>> _compiledQuery =

    CompiledQuery.Compile((SchoolEntities ctx, decimal budget) =>

        (from d in ctx.Departments

         where d.Budget > budget

         select d));

We declare a Func delegate which will hold the compiled query as a static
member and we use the CompiledQuery object to with the Compile
method to compile the LINQ query.
When we want to use the compiled query we do it by invoking it:

using (var context = new SchoolEntities())

{

    var query = _compiledQuery.Invoke(context, 150000);

    foreach (var item in query)

    {

        Console.WriteLine(item.Name);

    }

}

CompiledQuery Restrictions

Currently there are few restrictions that you must know when you
are using a CompiledQueries:

• No compilation is being done up until the first execution of the
  query. This means that in the first execution of a CompiledQuery
  we will get a downgrade of performance (because of the
  compilation). The other executions will much faster.
  There is a side affect of this restriction. When combined with Merge
  options the first Merge option that you pass with the first context
  will be saved and will be used in the other executions.
  For example running the following code for the first compiled query
  execution will cause the next running to use the
  MergeOption.NoTracking:

context.Departments.MergeOption = MergeOption.NoTracking;

var query = _compiledQuery.Invoke(context, 150000);

• Changes to the query such as compositions or calling for methods like
  First(), FirstOrDefault() and more will produce a not complied query.
  You must use the query as is.
  If you want to avoid this problem call the AsEnumerable() method
  and transform the query to LINQ to Objects query.

Summary

Lets sum up, CompiledQuery is a very powerful tool for performance
when you use Entity Framework. You should follow the restrictions
I wrote when you use them in your code.