linq to entities generated sql

问题:

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?

回答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

回答2:

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...

回答3:

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"... :-(

回答4:

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);

回答5:

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?

回答6:

You can use a real like in Link to Entities quite easily

Here's what's needed to make it work:

Add

    <Function Name="String_Like" ReturnType="Edm.Boolean">
      <Parameter Name="searchingIn" Type="Edm.String" />
      <Parameter Name="lookingFor" Type="Edm.String" />
      <DefiningExpression>
        searchingIn LIKE lookingFor
      </DefiningExpression>
    </Function>

to your EDMX in this tag:

edmx:Edmx/edmx:Runtime/edmx:ConceptualModels/Schema

Also remember the namespace in the <schema namespace="" /> attribute

Then add an extension class in the above namespace:

public static class Extensions
{
    [EdmFunction("DocTrails3.Net.Database.Models", "String_Like")]
    public static Boolean Like(this String searchingIn, String lookingFor)
    {
        throw new Exception("Not implemented");
    }
}

This extension method will now map to the EDMX function.

More info here: http://jendaperl.blogspot.be/2011/02/like-in-linq-to-entities.html