LINQ to SQL and the procedure cache of SQL Server

I just received a mail from Adam Machanic that pointed me to this bug (I would call it a performance issue) about the construction of SQL statements generated by the LINQ to SQL engine.

The issue: every string passed as a constant in the query will be auto-parameterized using the length of the passed string, even when you used a string variable into the LINQ query. If you write something like

string s = “Wine”;
var query =
        from x in db.Products
        where x.ProductName == s
        select x;

you will see that a parameter of type NVARCHAR(4) will be passed to the generated SQL query. The next execution of query might contain a different value in the s parameter, and for this reason a different parameter type might be used: if the length of the string in the s variable changes, then the same query will be sent to SQL Server, but using a different type in the sp_executesql parameters .For example, a NVARCHAR(5) would be used whether s contains”Bread”.

The consequence of this behavior is that you could have a non-optimal performance from SQL Server and, more important, the procedure cache could be filled up with several copies of the same query, differing each other only in the length of the parameter type.

I agree with Adam: this is something to be fixed. But my suspect is that we will get a “by design” answer another time…