在我的项目中,我使用Entity Framework 4.4.0.0,但遇到了以下难题。我必须检查用户是否被激活。我的查询看起来像:
任何()
_context.Users.Any(u => u.Id == userId && u.IsActivated);
生成的sql是:
SELECT CASE
WHEN ( EXISTS (SELECT 1 AS [C1]
FROM [dbo].[Users] AS [Extent1]
WHERE ( [Extent1].[Id] = @p__linq__0 )
AND ( [Extent1].[IsActivated] = 1 )) ) THEN cast(1 AS BIT)
WHEN ( NOT EXISTS (SELECT 1 AS [C1]
FROM [dbo].[Users] AS [Extent2]
WHERE ( [Extent2].[Id] = @p__linq__0 )
AND ( [Extent2].[IsActivated] = 1 )) ) THEN cast(0 AS BIT)
END AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable1]
因为Count()
我得到这个查询:
SELECT [GroupBy1].[A1] AS [C1]
FROM (SELECT COUNT(1) AS [A1]
FROM [dbo].[Users] AS [Extent1]
WHERE ( [Extent1].[Id] = @p__linq__0 )
AND ( [Extent1].[IsActivated] = 1 )) AS [GroupBy1]
这看起来正确吗?我不像sql一样好...但是对我来说它看起来不是很有效。我想念什么吗?
是“ select count(*) from dbo.Users where id=@id and IsActivated=1
”低效率?
这取决于。
该EXISTS
实施不是很大无论是。如果有0
行,它将执行两次检查。在那种情况下,那COUNT
将是更好的选择,因为它只需搜索不存在的行并将其计数一次。
您可能会发现
_context.Users
.Where(u => u.Id == userId && u.IsActivated)
.Select(u=> true)
.FirstOrDefault();
给出了比两者都更好的计划(根据卢克的建议进行了修订)。在EF4上测试生成的查询的方式如下:
SELECT TOP (1) cast(1 AS BIT) AS [C1]
FROM Users
WHERE userId = @userId
AND IsActivated = 1
这意味着如果存在多个行,它不会处理不必要的其他行,而只会搜索与WHERE
一次匹配的行。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句