在实体框架5比较所有）与计数）生成的查询性能（（

问题描述

在项目中，我使用实体框架4.4.0.0和我有以下的困境。我要检查如果一个用户被激活。我的查询是这样的：

任何（）

  _context.Users 。任何（U => u.Id ==用户用户名与放大器;&安培; u.IsActivated）; 
  

生成的SQL是：

SELECT CASE
当（EXISTS（SELECT 1 AS [C1]
从[DBO]。[用户] AS [Extent1]
，其中（[Extent1]。[ID] = @ p__linq__0）
和（[Extent1]。[IsActivated] = 1）））再投（1 AS BIT）
当（NOT EXISTS（SELECT 1 AS [C1]
从[DBO]。[用户] AS [Extent2]
WHERE（[Extent2]。[ID] = @ p__linq__0）
和（[Extent2]。[IsActivated] = 1）））再投（0作为BIT）
端AS [C1]
FROM（SELECT 1 AS X）AS [SingleRowTable1]


有关 COUNT（）我得到这个查询：

SELECT [GroupBy1]。[A1] AS [C1]
FROM（SELECT COUNT（1）[A1]
起价[DBO]。[用户] AS [Extent1]
WHERE（[Extent1]。[ID] = @ p__linq__0）
和（[Extent1]。[IsActivated] = 1））AS [GroupBy1]


这是否看起来是对的？我不像SQL很好......但看起来不是很有效给我。我缺少的东西吗？

是'从dbo.Users SELECT COUNT（*），其中id = @ ID和IsActivated = 1 '低效率？

解决方案

这要看情况。

在 EXISTS 的实施是不是很大无论是。它会执行检查，如果两次有 0 行。在这种情况下， COUNT 人会更好，因为它只有以搜索不存在行，一次算什么。

您可能会发现，检查

  _context.Users 
。凡（U => u.Id = =用户id&功放;&安培; u.IsActivated）
。选择（U =>真）
 .FirstOrDefault（）; 
  

提供了比这两个更好的计划（修订如下卢克的建议）。在EF4生成的查询测试是沿

线 SELECT TOP（1）铸件（1 AS BIT）AS [C1]
用户的
其中userid = @userId
和IsActivated = 1


这意味着它不会处理不必要的额外行，如果存在多个，仅进行匹配行搜索 WHERE 一次。

In my project I use Entity Framework 4.4.0.0 and I have the following dilemma. I have to check if an user is activated. My query looks like:

Any()

_context.Users.Any(u => u.Id == userId && u.IsActivated);

The generated sql is:

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]

For Count() I get this query:

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]

Does this looks right? I am not very good as sql ... but it looks not very efficient to me. Am I missing something?

Is 'select count(*) from dbo.Users where id=@id and IsActivated=1' less efficient?

解决方案

It depends.

The EXISTS implementation isn't that great either. It will perform the check twice if there are 0 rows. In that case the COUNT one will be better as it only has to search for the non existent row and count it once.

You may find that checking

_context.Users
        .Where(u => u.Id == userId && u.IsActivated)
        .Select(u=> true)
        .FirstOrDefault();

gives a better plan than both (amended following Luke's suggestion). Testing on EF4 the query generated is along the lines of

SELECT TOP (1) cast(1 AS BIT) AS [C1]
FROM   Users
WHERE  userId = @userId
       AND IsActivated = 1

Meaning it doesn't process unnecessary additional rows if more than one exists and only performs the search for rows matching the WHERE once.

这篇关于在实体框架5比较所有）与计数）生成的查询性能（（的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！