query result what should i use Count() or Any()

Question

I am checking login of a user by this repository method,

  public bool getLoginStatus(string emailId, string password)
    {
        var query = from r in taxidb.Registrations
                    where (r.EmailId == emailId && r.Password==password)
                    select r;
        if (query.Count() != 0)
        {
            return true;
        }
        return false;
    }

I saw in one of the previous questions !query.Any() would be faster... Which should i use? Any suggestion....

Answer 1

The sql generated will be different between the two calls. You can check by setting your context.Log property to Console.Out or something.

Here's what it will be:

SELECT COUNT(*) AS [value]
FROM [dbo].[Registrations] AS [t0]
WHERE [t0].[EmailId] = @p0 and [t0].Password = @p1

SELECT 
    (CASE 
        WHEN EXISTS(
            SELECT NULL AS [EMPTY]
            FROM [dbo].[Registrations] AS [t0]
            WHERE [t0].[EmailId] = @p0 and [t0].Password = @p1
            ) THEN 1
        ELSE 0
     END) AS [value]

In this case, I doubt it will make any difference because EmailID is probably a unique index so there can only be 1 result. In another case where count can be > 1, Any would be preferable because the second query allows sql server to short circuit the search since it only needs to find one to prove that any exist.

Answer 2

You could express it quite a bit shorter like this:

return taxidb.Registrations.Any(r => r.EmailId == emailId && r.Password==password);