Upgrading EF projects to EF4? Don't forget to target .NET 4.0

Friday, May 22 2009 - Data Access

Previous "What's New in EF 4 Posts"

ef4

 

When testing some of the query compilation the other day in my post Checking for EF to TSQL Query Compilation Changes in VS2010 Beta1, I used the code from my Tech Ed EF Gotchas session to test the new version of EF.

Although my project imported into VS2010 perfectly, I must not have said "yes" to the question about targetting .NET 4.0 during the import.

Because of this, I was using the previous compiler when running my tests! Thanks to Kati for pointing this out.

The results of one of the tests were incorrect - and it was the one I was surprised about.

Using the proper APIs, the fix to lever store query caching is in fact in the Beta 1 bits.

As a refresher, when creating a parameterized query such as this:

string param = "Smith"; 
IEnumerable<Person> FiveLetterPeople = 
        from p in context.People 
        where p.LastName == param select p; 
List<Person> pList = FiveLetterPeople.ToList();

the length of the parameter became part of the query that went to the store. Because the length was hard coded (in this case, 5) into the query, it is stored into the SQL SErver Cache that way. The same query with a different parameter, eg "Lerman", will not leverage the previously cached query because its length is 6.

With EF4, I am guessing that it was not possible to remove the length and therefore a default length of 4000 is used.

Here is the critical part of the query that hits the database

WHERE [Extent1].[LastName] = @p__linq__0',N'@p__linq__0 
nvarchar(4000)',@p__linq__0=N'Smith'

The next query to come in has the following predicate

WHERE [Extent1].[LastName] = @p__linq__0',N'@p__linq__0 
nvarchar(4000)',@p__linq__0=N'Lerman'

which allows SQL Server to use the previously cached query.

I've been told that the performance is dramatically improved because of this change.

Tagged as: ,