我遇到了实体框架的奇怪行为。
当我的代码如下所示时:
var smithsOnly = false;
var partners = ctx.Partners.Where(x => !smithsOnly || x.LastName == "Smith").ToList();
生成的 SQL 非常明显:
SELECT
[Extent1].[FirstName] AS [FirstName],
[Extent1].[LastName] AS [LastName]
FROM [dbo].[PartnerEntities] AS [Extent1]
WHERE (@p__linq__0 <> 1) OR (N'Smith' = [Extent1].[LastName])
如果我使用三元运算符 ? : ,我的代码更改如下:
var smithsOnly = false;
var partners = ctx.Partners.Where(x => smithsOnly ? x.LastName == "Smith" : true).ToList();
正如人们所期望的那样,SQL 结果更加复杂:
SELECT
[Extent1].[FirstName] AS [FirstName],
[Extent1].[LastName] AS [LastName]
FROM [dbo].[PartnerEntities] AS [Extent1]
WHERE (CASE
WHEN (@p__linq__0 = 1) THEN CASE
WHEN (N'Smith' = [Extent1].[LastName]) THEN CAST(1 AS bit)
WHEN (NOT ((N'Smith' = [Extent1].[LastName]) AND
([Extent1].[LastName] IS NOT NULL))) THEN CAST(0 AS bit)
END
ELSE CAST(1 AS bit)
END) = 1
我想知道,为什么实体框架在最里面的CASE
中添加第二个WHEN
,否定条件和非空检查,而不是简单地ELSE CAST(0 AS bit)
?我知道 SQL Server 中的运算符=
与 C# 中的==
null 值不同(NULL = NULL
提供NULL
,而不是true
),但在我的情况下,无论如何都会过滤掉NULL
LastName
的Person
,'Smith'
是一个常量,而不是变量。当[Extent1].[LastName]
与变量或其他列进行比较时[Extent1].[FirstName]
确实需要额外的CASE
或非空检查CASEs
。
这只是缺少性能调整吗?
上面的查询可以用更简单的方式编写,它只是对问题呈现的更复杂的查询的简化。
您应该动态创建查询。像这样:
IQueryable<Partner> partners; // use the same type as ctx.Partners has
if (smithsOnly)
partners = ctx.Partners.Where(x => x.LastName == "Smith");
else
partners = ctx.Partners;
var list = partners.ToList();