CODEWITHSUNDEEP
entity-frameworkentity-framework-4linq-to-entities
Bernhard Koenig
Bernhard Koenig

Reputation: 1382

Entity Framework .Any does not generate expected SQL WHERE clause

Entity Framework and Linq-To-Entities are really giving me some headaches. I have a fairly simple query:

var result = feed.FeedItems.Any(ei => ei.ServerId == "12345");

feed is a single EF entity I selected earlier in a separate query from the same context.

But the generated SQL just throws away the .Any condition and requests all FeedItems of the feed object which can be several thousands of records which is a waste of Network bandwith. Seems the actual .Any comparison is done in C#:

exec sp_executesql N'SELECT [t0].[Id], [t0].[FeedId], [t0].[ServerId], [t0].[Published], [t0].[Inserted], [t0].[Title], [t0].[Content], [t0].[Author], [t0].[WebUri], [t0].[CommentsUri]
FROM [dbo].[FeedItem] AS [t0]
WHERE [t0].[FeedId] = @p0',N'@p0 int',@p0=3

I also tried:

!feed.FeedItems.Where(ei => ei.ServerId == "12345").Any();

But it doesn't change anything. Even removing Any() and querying for the complete list of items does not change the query.

I don't get it ... why isn't this working as I would expect? There should be a

WHERE ServerId == 1234

clause in the SQL statement.

Thanks very much for any help/clarification :)

Upvotes: 1

Views: 1178

Answers (1)

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236318

As Nicholas already noticed, looks like query executed in FeedItems property (possibly you are returning List or IEnumerable) and whole list of items are returned from database. After that you are applying Any to in-memory collection. That's why you don't see WHERE ServerId == 1234 in SQL query.

When you apply Any to IQueryable generated query will look like:

SELECT 
    (CASE 
        WHEN EXISTS(
            SELECT NULL AS [EMPTY]
            [dbo].[FeedItem] AS [t0]
            WHERE [t0].[ServerId] = @p0
            ) THEN 1
        ELSE 0
     END) AS [value]

Upvotes: 3

Related Questions