L2S and EF parameterization problem fixed in .NET 4.0 – almost

One of the first things that I did after installing VS2010 beta this week was to check on a parameterization problem with string parameters, and Linq To SQL/ADO.NET Entity Framework generated code. In the original version, this L2S query:

var x = from a in ents.authors
        where a.au_lname == "Smith"
        select new { a.au_lname, a.au_fname };

or this EF query:

string name = "Smith";
var x = from a in ents.authors
        where a.au_lname == name
        select new { a.au_lname, a.au_fname };

Would cause plan cache pollution and possibly performance problems. These queries produced a query plan with the parameter specified as NVARCHAR(5). Each query with a different length will produce a new plan in the cache, and the use of NVARCHAR rather than VARCHAR will cause a table scan (rather than a seek) in the plan, regardless of how many rows are in the table, because there must be a conversion of au_lname from VARCHAR to NVARCHAR as part getting the rows, making the query non-sargable. The correct database data type (VARCHAR) needs to be specified as the parameter data type.

In .NET 4.0/VS2010 Beta 1, L2S handles this, producing a VARCHAR(8000) parameter, as SQL Server's autoparameterization does. Entity Framework produces NVARCHAR(4000), fixing the length problem but still using the a different data type than the table specifies.

NOTE: This will be fixed in the next beta version of .NET 4.0 to produce VARCHAR(8000). See complete post here.

In my next installment of my MSDN "Under The Table", I'll be writing about the performance implications of data access method code in detail. I do mention "fixed in the next release" WRT L2S and EF, and hope that it will be resolved before .NET 4.0 ships.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.