LINQ to Entities条件提供了奇怪的结果 [英] LINQ to Entities conditionals give weird results
问题描述
尝试在LINQ查询（使用Entityframework）中实现条件创建奇怪的查询。在某些情况下，这些查询超时，即使阈值设置为180秒：
Trying to implement conditionals in a LINQ query (with Entityframework) creates strange queries. In some cases these queries time out, even though the threshold is set to 180 seconds:
List<LogEntity> dataList = db.LogEntities.Where(x =>
x.Source == "Source" &&
(String.IsNullOrEmpty(from) || x.EventDate >= cFrom) &&
(String.IsNullOrEmpty(to) || x.EventDate <= cTo) &&
(String.IsNullOrEmpty(uid) || x.DomainUserLogin == uid) &&
(String.IsNullOrEmpty(cid) || x.CaseReference == cid) &&
(String.IsNullOrEmpty(searchtext) || x.Message.Contains(searchtext)))
.OrderByDescending(y => y.EventDate)
.Take(500)
.ToList<LogEntity>();
使用稍微优雅的if语句，我没有问题，查询在几秒钟内返回：
With somewhat less elegant if-statements, I get no issues, and the queries return in few seconds:
IQueryable<LogEntity> data = db.LogEntities.Where(x => x.Source == "Source");
if (!String.IsNullOrEmpty(from))
data = data.Where(x => x.EventDate >= cFrom);
if (!String.IsNullOrEmpty(to))
data = data.Where(x => x.EventDate <= cTo);
if (!String.IsNullOrEmpty(uid))
data = data.Where(x => x.DomainUserLogin == uid);
if (!String.IsNullOrEmpty(cid))
data = data.Where(x => x.CaseReference == cid);
if (!String.IsNullOrEmpty(searchtext))
data = data.Where(x => x.Message.Contains(searchtext));
data = data.OrderByDescending(x => x.EventDate).Take(500);
List<LogEntity> dataList = data.ToList<LogEntity>();
条件都是从一个querystring传递的，这就是为什么它们有时可能带有一个值，有时候
The conditionals are all passed from a querystring, which is why they may sometimes be carrying a value and sometimes not.
同样的问题出现在使用三元运算符如
The same issue arises when using ternary operators like
...Where(x => truth ? x.something == somevalue : x.something == anothervalue)
$ b $对于为什么这些内联条件表现如此糟糕，是否有合理的解释？
Is there any reasonable explanation as to why these inline conditionals perform so poorly?
推荐答案
当您使用LINQ对EF数据库编写查询时，它们看起来很自然，但在幕后，有查询翻译器解析您的LINQ查询并将其拆分为2部分：一个在sql服务器上执行，另一个在客户端上使用LINQ扩展。
When you write queries with LINQ on EF databases they looks very natural but behind the scene there's the query translator that parses your LINQ query and split it into 2 parts:one is executed on the sql server, another - on the client using just LINQ extensions.
当使用某些表达式时，查询翻译器无法转换为SQL（例如某些.NET函数），可将数据过滤最小化，您可能会将整个数据表下载到客户端并对其进行过滤。
When you use some expression that the query translator cannot translate to SQL (e.g. some .NET functions) it minimize the data filtering and you may end up with downloading the entire data table to the client and filtering it their.
在你写的第一个查询中，你使用（String.IsNullOrEmpty（from）|| x.EventDate> = cFrom）; from在LogEntities的外部，翻译人员不能对它的值进行任何假设，以及如何计算记录。因此，很可能您只需将完整的LogEntities下载到客户端并将其过滤到客户端。如果记录数量巨大，您将收到超时错误。
In the first query your wrote, you use (String.IsNullOrEmpty(from) || x.EventDate >= cFrom); "from" is external to the LogEntities and the translator couldn't do any assumption about it values and how it is calculated regarding the records. So, most likely, you just downloading the full LogEntities to the client and filter it to the client. If number of records is huge, you'll get the timeout error.
在第二个查询中，您加入了简单表达式 Where（x => x.DomainUserLogin == uid）; 这被清楚地翻译成sql。因此，您可以得到正确的sql查询，可以筛选sql server端的大多数记录。
In the second query you joined simple expressions Where(x => x.DomainUserLogin == uid); that is clearly translated to sql. So, you get the correct sql query that filter most records on the sql server side.
可以使用SQL分析器或VS工具（取决于VS版本，或启用登录EF查看执行的实际查询。
You can use the SQL profiler, or VS tools (depending on VS editions, or enabling logging in EF to see the actual query that are executed.
这篇关于LINQ to Entities条件提供了奇怪的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
