How to prevent EF-Linq from generating a different query for variables versus constants?

Question

I have a Linq-query that returns in LinqPad in approximately a minute (the first time) using hard integer-values. However, when I turn those hard integer-values into local variables, it takes forever (more than 20 minutes).

My basic questions are:

• Why/how does would a different SQL-query cause this much delay?
• Can I prevent this different SQL-query?

In my trying to solve this (see underneath), I ran into these questions:

• how can I use DefaultQueryPlanCachingSetting or EnablePlanCaching?
• how can I suppress/prevent query-compilation/optimization ?
• how do I verify if I am using EF5?

The query-difference seems to be already noticed, and the why is explained in an answer to that question. I also have this query-difference where I see an extra sub-SELECT (and some extra JOINs). And the query uses parameters ([Extent6].[SomeThingId] = @p__linq__0) in stead of hard integer-values (4 = [Extent6].[SomeThingId])

However, I am not sure why this would be a problem, and how I can prevent it. (I understand that the answer may be easier when providing the query or DB-layout, but this kind of sensitive material, and my question remains the same...)

I was assuming the problem is from some 'optimization' in the compilation/execution-plan when using parameters. I was trying using this suggestion to turn of the auto-compilation for L2E:

 db.ContextOptions.DefaultQueryPlanCachingSetting = false;
 //(db as IObjectContextAdapter).ObjectContext.DefaultQueryPlanCachingSetting = false;

I could not get this to work, so I tried to find out if I could use the also mentioned [ObjectQuery.EnablePlanCaching = false;], but I could not find out where/how to use it. (I can not get my IQueryable to cast to an ObjectQuery, nor my context.) Does anybody know how to use DefaultQueryPlanCachingSetting or EnablePlanCaching?

Another thing I tried was taking the SQL from the constant-value-Linq, and introduce some SQL-variables in there. That also worked fine, so I tried turning this in a stored procedure, which I could call from EF. But now the stored procedure also takes very long to run.

I am (also) wondering if I really have EntityFramework 5. (This is my first project using Linq, EntityFramework and ASP.MVC.) But I have turned that in a separate question.

Update: I changed the 'regular' stored procedure:

SELECT ... WHERE @Id  = [Extent6].[SomeThingId] ...

into a stored procedure with a dynamic SQL-statement:

@sql = 'SELECT ... WHERE ' + CAST(@Id AS VARCHAR) + ' = [Extent6].[SomeThingId] ...';
EXEC (@sql);

With this 'dynamic' stored procedure I do get very quick results. And since the parameters will be reasonably constant (they will probably change between once a day and once a week), I think this lack of optimization/caching is okay for performance. (However, I dislike putting business logic in a stored procedure.)

Answer 1

I found this post by Stuart Leeks about Entity Framework 5 controlling automatic query compilation. It mentions that DefaultQueryPlanCachingSetting did not make it past the Release Candidate for EF5, and you need to set the EnablePlanCaching property on the ObjectSets instead. It also has a simple example and a suggestion for a handy extension method. I'll quote Stuart Leeks' example, since 'regular' MSDN does not provide one yet. You can check his blog for the extension method.

ObjectQuery<Customer> customersNoCache = context.Customers;
customersNoCache.EnablePlanCaching = false;
var query1 = from customer in context.Customers
             where customer.Country == "UK"
             select customer;

Edit: I actually had to add some more code to get from a DbSet to an ObjectQuery. In line with previous Customer-example this would look like:

ObjectContext lObjectContext = ((IObjectContextAdapter)this).ObjectContext;
ObjectQuery<Customer> lDocCatgNoCache = lObjectContext.CreateObjectSet<Customer>();

But the generated SQL still contains the parameter-optimization. My guess would be that EnablePlanCaching only suppresses caching for generating SQL from Linq, and it does not even consider the cache on the SQL-side?

(Unfortunately, I did not have time to reasearch/test any further. I went along with the implemented the 'dynamic' stored procedure, and finished this project.)