LINQ TO SQL, ADO.NET Entity Framework, T-SQL
I have a few applications/websites running with LINQ to SQL and the other day I decided to go ahead and optimize some of the queries, etc and I found that the size for variable length data types is derived from the parameter value instead of the column actual size?
for example a column is defined as nvarchar(30). when I use linq to query that column with a value that's 5 characters long the parameter in sp_executesql will be defined as nvarchar(5)...this causes a major procedure cache bloat even for a small utility site that has a lot of queries
is there a way to optimize this?
In EF 1, it's the same as the L2S behavior you're seeing. In EF 4, a default length of 4000 is used, so the query won't bloat the cache for different param values.