Previous "What's New in EF 4 Posts"
I know that the very brainy Kati Iceava is working on improving the query compilation that SqlClient does when creating TSQL from EF queries, so I checked a few queries to see if the changes had reached the Beta yet. I looked at three different queries and saw that the changes I was looking for are not in this particular version so I won't bother worrying about them until we get the next rev.
QUERY:
from c in context.Customers.Include("Orders")
orderby c.Person.LastName
select c;
COMMENTS: This is a query that has to do a bunch of left outer joins to handle eager loading the child data and also ordering by reference data.
TSQL: I don't even know how it would be possible to improve this because there are very explicit requirements of the query. SO far, no change yet.
QUERY:
string param = "Smith";
IEnumerable<Person> FiveLetterPeople = from p in context.People
where p.LastName == param select p;
List<Person> pList = FiveLetterPeople.ToList();
param = "Thompson";
IEnumerable<Person> EightLetterPeople = from p in context.People where p.LastName == param select p;
pList = EightLetterPeople.ToList();
COMMENTS: This parameters in the two version of this query get sent along with their length (@p__linq__2 nvarchar(5)',@p__linq__1=N'Smith' for the first query and @p__linq__2 nvarchar(8)',@p__linq__2=N'Thompson' for the second). When SQL Server executes them, because the parameter length is there, SQL can't leverage the query cache. The two queries are cached separately.
TSQL: No change yet, but I know that this is getting fixed.
Update - This test was run targeting .NET 3.5, not .NET 4.0. Whoops. The fix is, indeed , in Beta 1. Please see my follow up post: Upgrading EF projects to EF4- Don't forget to target .NET 4.0
QUERY:
from c in context.Customers.Include("Orders")
where c.Person.LastName.StartsWith("M")
select c;
COMMENTS: StartsWith doesn't get converted very well into TSQL. I'm not sure if this is a problem in how the LINQ Expression tree or EF Expression tree are built or if it's a problem with how SqlClient transposes StartsWith. THere is no equivalent in TSQL and the method is translated to:
WHERE (CAST(CHARINDEX(N'M', [Join3].[LastName]) AS int)) = 1
This does not take advantage of indexing and is a very poorly performing query. While ESQL has a LIKE operator that you can use to force the TSQL to use LIKE in the predicate (WHERE LastName LIKE 'M%'), StartsWith doesn't "think" to use LIKE.
TSQL: No change yet. I know this is on Kati's radar and have high hopes that we'll see this query compile to a SARGable query in a later rev of VS2010.
