linq to entities generated sql

Question

I am having some problems with linq to entities in the ado.net entity framework. Basically what I'm doing is this:

var results = (from c in companies
 where c.Name.StartsWith(letter)
 select c);

and this gets translated to SQL as something like:

WHERE (CAST(CHARINDEX(@p, [Extent1].[Name]) AS int)) = 1

which is fine but my table has millions of records so this runs VERY slow. What I need it to generate is something like:

WHERE Name LIKE @p + '%'

I'm searched high and low and cannot find any solutions except to either use a stored procedure or use entity sql...

Is there any way to do this through linq? Possibly by somehow extending the linq to entities linq provider, or somehow intercepting the command tree or generated query?

Please help!

Answer 1

Wow, that is a truly bizarre way of doing it! Note that LINQ-to-SQL (in this case) uses LIKE @p0 + '%'... very odd.

Which EF provider (database) are you using? SQL Server?

As you say, a stored procedure will the the job, but you shouldn't have to do that... very, very odd...

Answer 2

Is "letter" a char? If you made it a string, what happens?

var results = (from c in companies
    where c.Name.StartsWith(letter.ToString())
    select c);

Answer 3

I tried using this syntax instead

Name.Substring(0, 1) == "E"

This SQL is generated

WHERE N'E' = (SUBSTRING([Name], 0 + 1, 1))

Maybe this is more efficient?

Answer 4

This is a known issue with Linq to Entities. Unlike LIKE, apparently this construct doesn't use indexes.

We've had some success using Substring (which translates to SUBSTRING). The execution plan is similar, but in our case the query executes far quicker.

It's another "I'm sure it will be fixed in EF 2"... :-(