CompiledQuery in Entity Framework

March 1, 2010

CompiledQuery in Entity Framework


There are timesCompiledQuery in Entity Framework
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.


Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>