c# - 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?

Answer 1

I am not a SQL expert but guessing that both syntaxes:

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

and

WHERE Name LIKE @p + '%'

will result in either a table scan or ideally an index scan. Bottom line they will perform the same. I verified this by viewing the execution plans below. Bottom line, you need to rethink your database schema or how your are performing your search. This is not a LINQ issue.

One possible area for improvement: insure that you have indexed the column that you are searching on.

alt text http://download.binaryocean.com/plan1.gif

alt text http://download.binaryocean.com/plan2.gif