Why is my Entity Framework query one slow?

Question

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

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

After several attempts, 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 sql from two queries, the second query does not use the nested SELECT and TOP operation. But I did not expect it to be 30 times faster just because of these two things. In addition, when making queries using SQL Server Management Studio, you cannot measure the difference.

When I look at the execution plan, they both create a clustered index search that has a 100% query cost. whereas the extra choice and the upper operation have a request cost of 0%. The request plan from EFProfiler says the same thing that it should not matter.

What can I do to better understand query performance in this case?

The following 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 is 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 really want is dbContext.Users.Find(id) - it will not even go to the database if it is not needed. See msdn for details .

Answer 2

when you say

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

Users are of type DbSet or its user collection, so it first selects the user collection while in
dbContext.Users.Where(u => u.Id == userId).ToList().Single(); it contains a condition for downloading it.

So, if there were 100 users, the first request will display 100 users and then it will filter, and in the second only 1.

Hope this helps.