Why is my Entity Framework query with Single slow?

Question

I have quite a simple query that is very slow. Entity Framework Profiler says it takes about 100 ms.

dbContext.Users.Single(u => u.Id == userId);

After trying around a bit I found a query that is very similar but much faster (about 3 ms).

dbContext.Users.Where(u => u.Id == userId).ToList().Single();

When I compare the sql of the two queries the second query does not use a nested SELECT and no TOP operation. But I would not expect it to be 30 times faster just because of these two things. Also when executing the queries using SQL Server Management Studio there is no difference measurable.

When I look at the execution plan they both make a clustered index seek which has 100% query cost. whereas the additional select and the Top operation have 0% query cost. The query plan from EFProfiler says the same indicating that it should not make any difference.

What can I do to get a better understanding about the query performance in this case?

Below is the resulting SQL for the first query.

SELECT [Limit1].[Id]                     AS [Id],
   [Limit1].[EmailAddress]           AS [EmailAddress],
   [Limit1].[FirstName]              AS [FirstName],
   [Limit1].[LastName]               AS [LastName]
FROM   (SELECT TOP (2) [Extent1].[Id]                     AS [Id],
                   [Extent1].[EmailAddress]           AS [EmailAddress],
                   [Extent1].[FirstName]              AS [FirstName],
                   [Extent1].[LastName]               AS [LastName]
    FROM   [dbo].[Users] AS [Extent1]
    WHERE  ([Extent1].[Id] = 'b5604f88-3e18-42a5-a45e-c66cc2a632d3' /* @p__linq__0 */)
           AND ('b5604f88-3e18-42a5-a45e-c66cc2a632d3' /* @p__linq__0 */ IS NOT NULL))    AS [Limit1]

Here the sql of the second (faster) query.

SELECT [Extent1].[Id]                     AS [Id],
   [Extent1].[EmailAddress]           AS [EmailAddress],
   [Extent1].[FirstName]              AS [FirstName],
   [Extent1].[LastName]               AS [LastName]
FROM   [dbo].[Users] AS [Extent1]
WHERE  ([Extent1].[Id] = 'b5604f88-3e18-42a5-a45e-c66cc2a632d3' /* @p__linq__0 */)
   AND ('b5604f88-3e18-42a5-a45e-c66cc2a632d3' /* @p__linq__0 */ IS NOT NULL)

Answer 1

What you actually want is dbContext.Users.Find(id) - this will not even go to the database if it does not have to. See more details on msdn.