Comparing performance of generated queries for Any() vs Count() in Entity Framework 5

Question

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?

Answer 1

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.