Entity Framework 6 auto-compiled queries

Question

I have a query that takes a little time on first run. All subsequent executions are very fast (even with different data). I believe that the first execution of EF automatically compiles the request (building a query plan, blah blah blah), and the second call uses the compiled version. This is normal except for the first user. This bad juice is screwed. On a bad day, EF execution will be 2 minutes (with 71ms it actually talks to SQL Server - it is checked using SQL Server Profiler.)

I went ahead and precompiled the views, which repaired the initial caching a bit.

Basically the query looks 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 not bother with the data model. But for the background, basically, each table is a “form”.

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

Besides warming EF on startup, I hope I don't have something basic? It seems that the pipeline for compiling the request is a pretty black box? Is there a way to connect to the query preparation process to find out what is happening? (Maybe something less decisive than capturing the source .. :)

Answer 1

I have 2 options:

• Use the famous sqlQuery, and just for this one query, execute it as raw sql through your DbContext, as described here :

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

• You can use a delegate that returns IQueryable 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. Starting with .NET 4.5, all your requests are cached as Func, as described above automatically, which explains why it runs smoothly the second time. Creating a delegate manually should solve your problem the first time you run the request.