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

Status : 

  Deferred<br /><br />
		The product team has reviewed this issue and has deferred it for consideration at a later time.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 708457 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 11/25/2011 3:31:35 AM
Access Restriction Public


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
                                                      hasChildren = u.Children.Count() > 0

                var hasChildren = hasChildrenQuery.ToList();

we get the following SQL generated:

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]
	[Project1].[Id] AS [Id], 
	[Project1].[C1] AS [C1], 
		COUNT(1) AS [A1]
		FROM [dbo].[Children] AS [Extent3]
		WHERE [Project1].[Id] = [Extent3].[UserId]) AS [C2]
		[Extent1].[Id] AS [Id], 
			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.
Sign in to post a comment.
Posted by Microsoft on 10/23/2012 at 1:27 PM
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.

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)