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

  • Article

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

  • Anonymous
    June 30, 2011
    Does it apply to CodeFirst also? As long as I know CodeFirst does not support CompileQuery.

  • Anonymous
    June 30, 2011
    @Hyojung Kwon: Code First just generates the XML - it doesn't affect the runtime so it should work just fine. [)amien

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

  • Anonymous
    June 30, 2011
    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.

  • Anonymous
    June 30, 2011
    hmmm... looks familiar <g> Grats on getting it live, looking forward to further work

  • Anonymous
    June 30, 2011
    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.

  • Anonymous
    June 30, 2011
    Wouldn't it be great if you guys put that in Linq 2 SQL too...

  • Anonymous
    June 30, 2011
    The comment has been removed

  • Anonymous
    July 01, 2011
    @Bart: <g>

  • Anonymous
    July 01, 2011
    @Hyojung: Yes, that is the case.

  • Anonymous
    July 01, 2011
    The comment has been removed

  • Anonymous
    July 03, 2011
    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?

  • Anonymous
    July 03, 2011
    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?

  • Anonymous
    July 04, 2011
    @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).

  • Anonymous
    July 04, 2011
    @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).

  • Anonymous
    July 09, 2011
    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

  • Anonymous
    July 19, 2011
    Nice work guys, The CompiledQuery option is good but automatic is better.

  • Anonymous
    August 03, 2011
    The comment has been removed

  • Anonymous
    December 17, 2011
    Does this feature impose any design constraints? E.g. does my query have to be declared static?

  • Anonymous
    February 16, 2012
    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

  • Anonymous
    April 02, 2012
    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.

  • Anonymous
    April 16, 2012
    I too want to find out if parametrized queries is cached. This is something I struggled to get working with.

  • Anonymous
    June 08, 2012
    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.