CODEWITHSUNDEEP
sql-serverentity-framework
Lukáš Novotný
Lukáš Novotný

Reputation: 9052

Performance of Any vs FirstOrDefault

So I've been digging in queries EF produces for our app and I noticed some strange sql generation of Any calls - they seem to be overly complicated. Any seem to generate case when (x) then 1 when (not x) then 0 end - why not just use else? This way SQL server has to execute the same query twice in cases where Any should return false. I came up with workaround that seem to be faster in those cases using Where(cond).Select(q => true).FirstOrDefault(), but I'm still interested in whether this is just oversight or it has some meaning I'm missing.

context.Books.Any(b => b.Id == bookId);

declare @p__linq__0 Int = 1;

SELECT 
CASE WHEN ( EXISTS (SELECT 
    1 AS [C1]
    FROM [Books] AS [Extent1]
    WHERE [Extent1].[ID] = @p__linq__0
)) THEN cast(1 as bit) WHEN ( NOT EXISTS (SELECT 
    1 AS [C1]
    FROM [Books] AS [Extent2]
    WHERE [Extent2].[ID] = @p__linq__0
)) THEN cast(0 as bit) END AS [C1]
FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]

context.Books.Where(b => b.ID == bookId).Select(b => true).FirstOrDefault();

declare @p__linq__0 Int = 1;

SELECT TOP (1) 
cast(1 as bit) AS [C1]
FROM [Books] AS [Extent1]
WHERE [Extent1].[ID] = @p__linq__0

Upvotes: 2

Views: 2570

Answers (1)

Sebastian Meine
Sebastian Meine

Reputation: 11813

After you have seen that something does not exists it is pretty clear that it does indeed not exists, so checking agin if is really does not exist is not necessary. This is just one of those cases where linq is producing overly complicated queries. Your workaround works, but it is not necessarily as easy to understand as one would like, so I suggest to take the performance hit of the original linq until it really becomes a performance problem. Just make sure there is an index on the Id column.

Upvotes: 1

Related Questions