Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
410 views
in Technique[技术] by (71.8m points)

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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...