Entity Framework 6 auto compiled queries

Question

I've got a query that is taking a bit of time on the first execution. All subsequent executions are super fast (even with different data). I believe the first execution EF is auto compiling the query (building the query plan, blah blah blah) and the second call is using the compiled version. Which is fine, except for the first user. That poor sap is screwed. On a bad day, the EF execution will be 2 minutes (with 71ms actually talking to SQL Server - verified with SQL Server Profiler.)

I've gone ahead and have precompiled the views, which did shave some time off the initial caching.

Basically the query looks a little something like this

dataSource.Component
    .Include(t => t.Table1)
    ... 37 tables later ...
    .Include(t => t.Table38)
    .Where(n=>n.Id == id).First()

Now I can't go a fiddling around with the data model. But for background, basically each table is a 'Form.'

The SQL Server Profiler output is basically a big UNION with the tables but the execution is very fast so I don't think the problem is the table layout/keys...

Other than warming EF on startup, I am hoping I am missing something basic? It seems like the pipeline for compiling the query is pretty black box? Is there a way to hook into the query prep process to see whats going on? (Maybe something less drastic than grabbing the source.. :)

Answer 1

You have 2 options in my opinion:

1. Use the known sqlQuery and just for this one query execute it as raw sql through your DbContext as documented here:

   datasource.Component.SqlQuery("...").ToList();
   

2. You can use a delegate which returns an IQueryable to be able to precompile your expression:

   public static Func<MyContext,int,IQueryable<Component>> CompiledQuery = (ctx,id) => 
          ctx.Include(c => c.Table1)
             .Include(c => c.Table2)
             ...
             .Include(c => c.Table38)
             .Where(n => n.Id == id);
   

   Then in your code you can use Invoke to use the query:

   using(var datasource = new MyContext())
   {
       var result = CompiledQuery.Invoke(datasource,2).ToList();
   }
   

The second option is probably best for you. Since .NET 4.5 all your queries are cached as a Func as described above automatically which explains why it runs smoothly the second time. Creating the delegate manually should resolved your issue the first time you run the query.