Entity Framework generates inefficient SQL for Any()

Posted by Microsoft on 10/23/2012 at 1:26 PM

Hello,
Thank you for taking the time to provide feedback. We addressed a lot of connect suggestions in our last release, but we weren’t able to address them all. We have copied this issue to our backlog and will consider it for a future release. You can view this suggestion on our public backlog – http://entityframework.codeplex.com/workitem/list/basic?keywords=DevDiv%20[Id=299943].
~Entity Framework Team

Posted by Piers Lawson on 11/25/2011 at 5:43 AM

To be explicit... the Count() > 0 does work if the > 0 executes back in the client code. It doesn't work well if it executes in the database, For example, having it as part of a projection makes the entire query run slow:

SLOW

new {
a,
HasChildren = a.Children.Any()
}

ALSO SLOW

new {
a,
HasChildren = a.Children.Count() > 0
}

FAST

new {
a,
Count = a.Children.Count()
}

Posted by Piers Lawson on 11/25/2011 at 2:47 AM

Also, the workaround suggested has issues as well!

Using Count() instead of Any() does produce efficient SQL...

BUT if you use Count() > 0 the code becomes over complicated again and performance drops. It effectively introduces an equivalent bug: it checks for both a count greater than zero and a count NOT greater than zero.

Posted by Piers Lawson on 11/25/2011 at 2:32 AM

Hopefully this can be easily fixed as it will not be obvious to many people that it is happening and is a huge performance problem... basically doubling the time required to execute the query!

Posted by Microsoft on 10/27/2011 at 11:58 AM

Thank you for reporting this issue. In general, when projecting what would be a SQL predicate, we translate it into a case statement to account for SQL’s three-valued logic (true, false or null). However, as you have noticed, as EXISTS cannot return null, in this case this is redundant. We are tracking the issue on our side and have it in the backlog for future releases.
In the meantime, here are some possible ways to work around this issue and avoid duplication of the query:

1. var result = context.Users.Where(u => u.UserId == userId).Count() > 0;

The comparison to 0 in this case would occur on the client.

2. var result = context.CreateQuery<bool>("select true from {1} as b where EXISTS(select 1 from Model1Container.Users as u where u.UserId == userId)").ToList().Count > 0;

In this case both the count and the comparison to 0 happen on the client.

Thank you,
Kati Iceva,
Entity Framework Developer

Posted by MS-Moderator07 [Feedback Moderator] on 10/19/2011 at 11:08 PM

Thanks for your feedback.

We are rerouting this issue to the appropriate group within the Visual Studio Product Team for triage and resolution. These specialized experts will follow-up with your issue.

Posted by MS-Moderator07 [Feedback Moderator] on 10/19/2011 at 10:56 PM

Posted by MS-Moderator01 on 10/19/2011 at 1:43 PM

Thank you for your feedback, we are currently reviewing the issue you have submitted. If this issue is urgent, please contact support directly(http://support.microsoft.com)

Posted by Adam Tegen on 7/26/2012 at 3:01 PM

public static class LinqExt
{
    public static bool BetterAny<T>( this IQueryable<T> queryable, Expression<Func<T, bool>> predicate)
    {
        return queryable.Where(predicate).Select(x => (int?)1).FirstOrDefault().HasValue;
    }
}

Then you can do this:
context.Users.BetterAny(u => u.UserId == userId);

Posted by Alexander Korolev on 12/6/2011 at 9:16 PM

I'm use intermediate projection as (collection.Any() ? 1 : 0)

1) Inefficient SQL:
Repository<User>().Select(u => new {
     u.Id,
     HasMembership = u.Memberships.Any()
})
    produce bad sql (19 logical reads in my database):
SELECT
1 AS [C1],
[Extent1].[Id] AS [Id],
CASE WHEN ( EXISTS (SELECT
    1 AS [C1]
    FROM [Security].[UserRole] AS [Extent2]
    WHERE [Extent1].[Id] = [Extent2].[UserId]
)) THEN cast(1 as bit) WHEN ( NOT EXISTS (SELECT
    1 AS [C1]
    FROM [Security].[UserRole] AS [Extent3]
    WHERE [Extent1].[Id] = [Extent3].[UserId]
)) THEN cast(0 as bit) END AS [C2]
FROM [Security].[User] AS [Extent1]

2) Workaround:
Repository<User>().Select(u => new {
    u.Id,
    HasMembership = u.Memberships.Any() ? 1 : 0
}).Select(e => new {
        e.Id,
        HasMembership = e.HasMembership != 0
})

produce good SQL with single loop in child table (11 logical reads):
SELECT
1 AS [C1],
[Project2].[Id] AS [Id],
CASE WHEN (0 <> [Project2].[C1]) THEN cast(1 as bit) WHEN (0 = [Project2].[C1]) THEN cast(0 as bit) END AS [C2]
FROM ( SELECT
    [Extent1].[Id] AS [Id],
    CASE WHEN ( EXISTS (SELECT
        1 AS [C1]
        FROM [Security].[UserRole] AS [Extent2]
        WHERE [Extent1].[Id] = [Extent2].[UserId]
    )) THEN 1 ELSE 0 END AS [C1]
    FROM [Security].[User] AS [Extent1]
) AS [Project2]

Entity Framework generates inefficient SQL for Any() by Amtiskaw