Search for existing suggestions

Bring back CompiledQuery in DbContext

CTP 5 dropped compiled query support, this is a huge mistake. Please make sure it is there in the final product.

103 votes
Sign in
Sign in with: facebook google
Signed in as (Sign out)

We’ll send you updates on this idea

greghroberts shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →


Sign in
Sign in with: facebook google
Signed in as (Sign out)
  • Erik commented  ·   ·  Flag as inappropriate

    Hi Diego,

    I figured out how to get an ObjectContext-oriented from a code-first model using the approach you mention. Unfortunately, I can't get the overhead to set all this up to under 2 minutes (see below) for our 1300-table model. But I do see the way forward for running compiled queries with a Code-First model, which is a plus for our modularity concerns.

    DBModel takes 88639.4403ms to run Build()
    DBModel takes 7949.1622ms to run Compile()
    ObjectContext takes 12419.9224ms to run the first query (compiled or not)

  • AdminDiego Vega (Program Manager, Microsoft Entity Framework) commented  ·   ·  Flag as inappropriate

    Hi Erik,

    Thanks for the feedback. If you need the full performance of CompiledQuery with Code First, you can still use ObjectContext with Code First. The pattern to make it work requires a bit more of code than with DbContext, e.g. there is no OnModelCreating method or auto-discovery of entity types like the one you get based on the DbSet properties you put in the DbContext, but you can new up a ModelBuilder, tell it all about your model using the Entity<T>() method and when it comes to create the context instance you can write code similar to this:


    Some notes on CreateObjectContext requirements are here:

    In the long run we would like to look at ways to close the perf gap between auto-compiled query and CompiledQuery. One idea in this regard is to attempt to create a query plan cache key based on the LINQ expressions directly without any further processing. We know that we cannot successfully do it for all LINQ expressions (because sometimes some evaluation is required to find out the full semantics of the query), but if we can do it for at least a large percentage of queries that would be a big gain and we can still fall back to the current implementation when that is not possible.

  • Erik commented  ·   ·  Flag as inappropriate

    Adding my vote. Code first looked like a great candidate to solve the modularity problems with ObjectContext and very large models. But so long as we have one compiled query in our app (and we have thousands), we can't use a DbContext. I would have to replace all our expressions and references to the compiled query delegate with inline Linq and then hope the query cache is tuned to handle our application size.

  • AdminDiego Vega (Program Manager, Microsoft Entity Framework) commented  ·   ·  Flag as inappropriate

    Alexandre is right. With few exceptions EF 5.0 automatically caches all LINQ queries, so you might want to repurpose your votes for other ideas.

    Here are some more details:

    CompiledQuery can be used to improve performance of LINQ queries by skipping the process of computing the translation of a query after the query is first executed. However, CompiledQuery requires cumbersome variable declarations and nested lambda syntax that leads to less cleaner code than regular LINQ queries.

    Rather than enabling CompiledQuery on queries produced by the DbContext API we decided it was better to invest on making CompiledQuery less necessary in EF 5.0: We improved LINQ to Entities to be able to cache the query translation automatically for most regular inline LINQ queries.

    There is still a small gap between this automatic caching and real CompiledQuery though. For example, in internal testing a benchmark that executes 5000 time a query like this: context.Customers.First(c => c.Id == id) with 5000 different values for id resulted in the following times:

    - CompiledQuery in EF 5.0: 1.85 seconds
    - Auto-compiled query in EF 5.0: 2.76 seconds
    - Without caching in EF 4.0: 16.68 seconds

  • Alexandre commented  ·   ·  Flag as inappropriate

    In the next version I heard that all queries by default will be compiled. I may be wrong I need to look into this again.

  • Ryan commented  ·   ·  Flag as inappropriate

    Why was the compiled query feature removed? Is there an alternative way to achieving the same performance benefits? If not, please bring it back.

Feedback and Knowledge Base