LINQ到实体生成的sql [英] linq to entities generated sql

问题描述

我有一些问题的LINQ to在ado.net实体框架的实体。基本上我在做什么是这样的：

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

和这个被转换为SQL作为是这样的：

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名称LIKE @p +'％'

WHERE Name LIKE @p + '%'

我M搜查高和低与除了无法找到任何解决方案要么使用存储过程或使用实体SQL ...

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

有没有办法通过LINQ做到这一点？可能通过某种方式延长LINQ到实体LINQ提供程序，或以某种方式拦截命令树或生成的查询？

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?

请帮帮忙！

推荐答案

我不是一个SQL专家，但猜测这两个语法：

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

WHERE（CAST（CHARINDEX（@p， [Extent1]。[名]）为int））= 1

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

和

WHERE名称LIKE @p +'％'

WHERE Name LIKE @p + '%'

将导致无论是表扫描或理想的索引扫描。底线他们将执行相同的。我通过查看下面的执行计划验证了这一点。底线是，你需要重新考虑你的数据库模式，或者你是如何在执行搜索。这是不是一个LINQ问题

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.

这篇关于LINQ到实体生成的sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！