How can we improve Entity Framework or Entity Framework Core?

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.

210 votes
Vote
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    I agree to the terms of service
    Signed in as (Sign out)
    You have left! (?) (thinking…)
    greghrobertsgreghroberts shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    12 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      I agree to the terms of service
      Signed in as (Sign out)
      Submitting...
      • ErikErik 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)

      • Diego VegaAdminDiego Vega (Program Manager, 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:

        modelBuilder
        .Build(storeConnection)
        .Compile()
        .CreateObjectContext<MyObjectContextType>()

        Some notes on CreateObjectContext requirements are here: http://msdn.microsoft.com/en-us/library/system.data.entity.infrastructure.dbcompiledmodel(v=vs.103).aspx

        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.

      • ErikErik 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.

      • Diego VegaAdminDiego Vega (Program Manager, 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

      • AlexandreAlexandre 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.

      • RyanRyan 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