Auto-Compiled LINQ Queries (Entity Framework June 2011 CTP)


When you write a LINQ to Entities query today, the Entity Framework walks over the expression tree generated by the C#/VB compiler and translates (or compiles) that into SQL.

 

 

Compiling the expression tree into SQL involves some overhead though, particularly for more complex queries.  In order to avoid having to pay this performance penalty every time the LINQ query is executed, the CompiledQuery class was introduced.  This allows you to pay the overhead of compilation just once, and gives you back a delegate that points directly at the compiled version of the query in the Entity Framework cache.

 

static readonly Func<NorthwindEntities, IQueryable<string>> query =

   CompiledQuery.Compile<NorthwindEntities, IQueryable<string>>(

                                                        db => from c in db.Customers

                                                              where c.Country == “USA”

                                                              select c.CompanyName);

 

Each subsequent time you execute the query it’s now much faster.

 

Auto-Compiled LINQ to Entities Queries

The Entity Framework June 2011 CTP supports a new feature called Auto-Compiled LINQ Queries.  Now every LINQ to Entities query that you execute automatically gets compiled and placed in EF’s query cache.   Each additional time you run the query, EF will find it in its query cache and won’t have to go through the whole compilation process again.

 

This also provides a boost to queries issued using WCF Data Services, as it uses LINQ under the covers.

 

How Does it Work?

The Entity Framework will walk the nodes in the expression tree and create a hash which becomes the key used to place it in the query cache.  If it does not find the query in the cache then it will go ahead and compile it and store the compiled query in the cache for subsequent use.  Each subsequent time, the hash will be calculated and find the compiled query in the tree, thus saving the compilation overhead.

 

Turning off Query Caching

A new property on ObjectContext.ContextOptions has been introduced that allows you to control the default behavior of query compilation.  By default this property is true, but you can set it to false to disable auto-compilation of LINQ queries:

 

db.ContextOptions.DefaultQueryPlanCachingSetting = false;

 

Note: If you’re using DbContext and need to get the ObjectContext instance, just cast to IObjectContextAdapter:

 

((IObjectContextAdapter)db).ObjectContext.ContextOptions.DefaultQueryPlanCachingSetting = false;

 

The value of this property gets propagated to the EnablePlanCaching property on each newly-created ObjectQuery for the ObjectContext.  Note: There is a known limitation in the June 2011 CTP: while DefaultQueryPlanCachingSetting is propagated to the EnablePlanCaching setting of each new query, the EnablePlanCaching setting is not propagated when using LINQ operators.

 

Performance Guidance

Obviously when it comes to performance there’s no one-size-fits-all approach.  The size/complexity of your application and queries will greatly influence how much of a performance boost your application will get from this.  It’s important to measure your specific application with a good profiler.

 

It is worth noting that using the CompiledQuery class is still the fastest approach, as the delegate that you get back does not need to calculate a hash of the expression tree.  Auto-compiled LINQ queries should be almost as fast though, and provide the benefit of not having to manually use the CompiledQuery class.

 

Feedback

We’d love to get your feedback on this feature and hear if it’s helping in your application.  Please download the CTP today and let us know what you think!

 

Entity Framework Team

Comments (23)

  1. Hyojung Kwon says:

    Does it apply to CodeFirst also? As long as I know CodeFirst does not support CompileQuery.

  2. Damien Guard says:

    @Hyojung Kwon: Code First just generates the XML – it doesn't affect the runtime so it should work just fine.

    [)amien

  3. Hyojung Kwon says:

    @Damien : Sorry, I still don't understand exactly. Do you mean that Code First or DbContext supports compiledquery directly in EF June 2011 CTP?

  4. divega says:

    Hyjung, you don't get to use CompiledQuery manually but you get automatic caching of LINQ queries that is compatible with Code First in the June 2011 CTP of EF (for the reasons Damien explained). Here is a link the CTP announcement if you want to give it a try: blogs.msdn.com/…/announcing-the-microsoft-entity-framework-june-2011-ctp.aspx.

  5. Bart Elia says:

    hmmm… looks familiar <g>

    Grats on getting it live, looking forward to further work

  6. Hyojung Kwon says:

    So, all LINQ queries of EF will be compiled regardless context is inherited from ObjectContext or DbContext, right?

    If so, it will be really great news.

  7. Wouldn't it be great if you guys put that in Linq 2 SQL too…

  8. Andrew says:

    what if the query is c.Country=="Indonesia"

    or c.Country=="Netherland"

    it will be cached one by one ?

  9. divega says:

    @Bart: <g>

  10. divega says:

    @Hyojung: Yes, that is the case.

  11. divega says:

    @Andrew: yes, EF translates constants in .NET to constants in SQL by design. If you want the query to be parametrized just use a variable, e.g.:

    var country = "Indonesia";

    var q = context.Branches.Where(b => b.Country == country);

    ShowResults(q);

    country = "Netherlands";

    ShowResults(q);

  12. John Rusk says:

    Looks great.  We've been using a homemade implementation of the same idea in LINQ to SQL.  It's been working great and made a huge difference to our application.

    Does the EF CTP also compile the implicit queries that are used by lazy loading when traversing properties?

  13. John Rusk says:

    What about things like DateTime.Now?  Will programmers have to use variables to avoid problems like this social.msdn.microsoft.com/…/f8a8aaa6-6075-4402-aeae-50c1eb3a61fb ?  

    If so, is there anything you can do to prevent them forgetting, especially when converting existing code to use auto-compilation?

  14. divega says:

    @John: as far as I remember, EF already cached queries created to perform lazy loading before we added this feature (keep in mind that eSQL queries have been cached automatically from the first version).

  15. divega says:

    @John: Re DateTime.Now, EF actually recognizes this pattern and translates it to a server-side expression (e.g. SysDateTime() in the case of SQL Server). In general EF throws runtime exceptions when it finds expressions in a query that it cannot translate rather than evaluating the expression on the client.

    What is interesting about CompiledQuery is that it is forced to take a snapshot of any sub-expression that you don't explicitly make a parameter of the CompiledQuery, e.g.:

    var g = 1;

    Func<MyContext, IQueryable<Product>> cq = CompiledQuery.Compile(

       (MyContext ctx) => ctx.Products.Where(p => p.Group == g));

    // this will return all products with Group ==1

    foreach(var p in cq(context))

    {

    }

    / this will also return all products with Group ==1

    // because the value 1 is now a constant in the query rather than a captured variable

    g = 2;

    foreach(var p in cq(context))

    {

    }

    On the other hand, regular LINQ to Entities queries will capture any variables as parameters, e.g.:

    var g = 1;

    // this will return all products with Id == 1

    var q = ctx.Products.Where(p => p.Group == g);

    foreach(var p in q)

    {

    }

    // this will return all products with Id == 2

    g = 2;

    foreach(var p in q)

    {

    }

    This difference between CompiledQuery and inline LINQ queries is not new, but it can certainly affect the behavior of your program if you are removing CompiledQuery. On the bright side, when you define a CompiledQuery you usually do it as a static member of a class, so there are no local variables and hence no snapshot of their values ever get taken.

    In any case, my recommendation to anyone removing CompiledQuery from an existing program would be to test that the performance of automatic caching of LINQ queries is good enough for them (as explained in this post, there is an overhead and using CompiledQuery is still more efficient).

  16. Venkatesh. S says:

    Hi,

    Is there way I can use CompilesQuery in Ef4.1 code first without this June CTP. We have an upcoming application quite database intensive hence from performance standpoint I would like to use that. Is that possible with some work around like what we do for stored procs

    Regards

    Venkatesh

  17. Craig Richards says:

    Nice work guys, The CompiledQuery option is good but automatic is better.

  18. How to Design a Website says:

    This allows you to pay the overhead of compilation just once, and gives you back a delegate that points directly at the compiled version of the query in the Entity Framework cache.

    _________________

    James

    <a href=“howtodesignawebsite.org.uk” rel=“dofollow”> How to Design a Website</a>

  19. Ben says:

    Does this feature impose any design constraints?

    E.g. does my query have to be declared static?

  20. Mathias says:

    Will parametrisized queries have the same hashes when used with different parameter values when released? So EF can recognize the same logical query executed multiple times with different parameter values? We tried the CTP, but it seems this is not the case.

    Query plan generation is a major performance bottleneck in our project, specifically ELinqQueryState.GetExecutionPlan() is killing us. I wrote about our problems in this post: social.msdn.microsoft.com/…/055c5b55-2d2a-4298-a28c-32737c57ad8d

    If someone could take a look at it, I'd appreciate it. Thanks

    ps. Keep up the good community involvment efforts, they are much appreciated

  21. Is it possible to take advantage of auto-compiled queries across ObjectContext's?

    We use WCF RIA Services and while we could start reusing our domain services, we don't want all entities loaded by previous queries to be tracked and remain in memory.

  22. WhatMathiasSaid says:

    I too want to find out if parametrized queries is cached. This is something I struggled to get working with.

  23. Prerequisits says:

    Are there any prerequisites for the automatic compilation to work?  On my local machine, I'm seeing the speed increase after the first hit (where it compiles).  But when I deploy to a Windows 2003 server with .Net 4.0, I don't see the same speed increase after the first hit.