Home Dashboard Directory Help
Search

Entity Framework Generates Inefficient SQL When Using Boolean Operators in Projections by Piers Lawson


Status: 

Closed
 as Deferred Help for as Deferred


8
0
Sign in
to vote
Type: Bug
ID: 708457
Opened: 11/25/2011 3:31:35 AM
Access Restriction: Public
Moderator Decision: Sent to Engineering Team for consideration
1
Workaround(s)
view
0
User(s) can reproduce this bug

Description

If I create a query containing a boolean operator the SQL generated tests for both a TRUE and a FALSE outcome. This is OK if one side of the operator could resolve to NULL, but not if both sides cannot be NULL (in which case the SQL should test for the TRUE and assume that otherwise the result is FALSE. For example in this query:

                var hasChildrenQuery = from u in db.Users
                                     select new
                                                 {
                                                     u.Id,
                                                     hasChildren = u.Children.Count() > 0
                                                 };


                var hasChildren = hasChildrenQuery.ToList();

we get the following SQL generated:

SELECT
1 AS [C1],
[Project2].[Id] AS [Id],
CASE WHEN ([Project2].[C1] > 0) THEN cast(1 as bit) WHEN ( NOT ([Project2].[C2] > 0)) THEN cast(0 as bit) END AS [C2]
FROM ( SELECT
    [Project1].[Id] AS [Id],
    [Project1].[C1] AS [C1],
    (SELECT
        COUNT(1) AS [A1]
        FROM [dbo].[Children] AS [Extent3]
        WHERE [Project1].[Id] = [Extent3].[UserId]) AS [C2]
    FROM ( SELECT
        [Extent1].[Id] AS [Id],
        (SELECT
            COUNT(1) AS [A1]
            FROM [dbo].[Children] AS [Extent2]
            WHERE [Extent1].[Id] = [Extent2].[UserId]) AS [C1]
        FROM [dbo].[User] AS [Extent1]
    ) AS [Project1]
) AS [Project2]

As you can see it is checking if the Count() is greater than zero and also if the Count() is NOT greater than zero. Since Count() can never be NULL and 0 can never be NULL, then one of these checks is redundant.

This can cause a significant decrease in performance.
Details
Sign in to post a comment.
Posted by Microsoft on 10/23/2012 at 1:27 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=324401].
~Entity Framework Team
Posted by Microsoft on 4/23/2012 at 9:05 PM
Hello Piers,

Thanks for taking the time to raise this issue. We agree that this would a great optimization for Entity Framework queries. However given where we are in this release we have decided to postpone it.

We will keep the work item active for a future release.

Thanks,
Entity Framework Team
Posted by Microsoft on 12/2/2011 at 12:16 AM
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 Piers Lawson on 11/29/2011 at 7:05 AM
A sample project is now attached
Posted by MS-Moderator10 [Feedback Moderator] on 11/28/2011 at 2:32 AM
Thank you for submitting feedback on Visual Studio 2010 and .NET Framework. In order to efficiently investigate and reproduce this issue, we are requesting additional information outlined below.

Could you please give us a demo project to demonstrate this issue?

We look forward to hearing from you with this information.

Microsoft Visual Studio Connect Support Team
Posted by MS-Moderator01 on 11/25/2011 at 3:44 AM
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)
Sign in to post a workaround.
Posted by Alexander Korolev on 12/6/2011 at 9:26 PM
Try this:

hasChildren = u.Children.Any() ? 1 : 0

(see also http://connect.microsoft.com/VisualStudio/feedback/details/695744/entity-framework-generates-inefficient-sql-for-any)
File Name Submitted By Submitted On File Size  
EntiftyFrameworkBooleanIssue.zip 11/29/2011 768 KB