1

Closed

[Performance] Produce simpler query for C# null comparison semantics, when NULL validation isn't necessary

description

Using this example in EF 6.0.2
int a = 3;
int? b = 3;
context.Request.Where(x => x.IdFloorNullAble == 3).Select(x=> 1).ToList(); //1
context.Request.Where(x => x.IdFloorNullAble == a).Select(x=> 1).ToList(); //2
context.Request.Where(x => x.IdFloorNullAble == b).Select(x=> 1).ToList(); //3

context.Request.Where(x => x.IdContactNotNull == 3).Select(x=> 1).ToList(); //4
context.Request.Where(x => x.IdContactNotNull == a).Select(x=> 1).ToList(); //5
context.Request.Where(x => x.IdContactNotNull == b).Select(x=> 1).ToList(); //6

#1
SELECT 1 AS [C1]
FROM   [dbo].[Request] AS [Extent1]
WHERE  (3 = [Extent1].[IdFloorNullAble])
       AND ([Extent1].[IdFloorNullAble] IS NOT NULL)

#2
SELECT 1 AS [C1]
FROM   [dbo].[Request] AS [Extent1]
WHERE  (([Extent1].[IdFloorNullAble] = 3 /* @p__linq__0 */)
        AND (NOT ([Extent1].[IdFloorNullAble] IS NULL
                   OR 3 /* @p__linq__0 */ IS NULL)))
        OR (([Extent1].[IdFloorNullAble] IS NULL)
            AND (3 /* @p__linq__0 */ IS NULL))

#3
SELECT 1 AS [C1]
FROM   [dbo].[Request] AS [Extent1]
WHERE  (([Extent1].[IdFloorNullAble] = 3 /* @p__linq__0 */)
        AND (NOT ([Extent1].[IdFloorNullAble] IS NULL
                   OR 3 /* @p__linq__0 */ IS NULL)))
        OR (([Extent1].[IdFloorNullAble] IS NULL)
            AND (3 /* @p__linq__0 */ IS NULL))

#4
SELECT 1 AS [C1]
FROM   [dbo].[Request] AS [Extent1]
WHERE  3 = [Extent1].[IdContactNotNull]

#5
SELECT 1 AS [C1]
FROM   [dbo].[Request] AS [Extent1]
WHERE  ([Extent1].[IdContactNotNull] = 3 /* @p__linq__0 */)
       AND (3 /* @p__linq__0 */ IS NOT NULL)

#6
SELECT 1 AS [C1]
FROM   [dbo].[Request] AS [Extent1]
WHERE  ([Extent1].[IdContactNotNull] = 3 /* @p__linq__0 */)
       AND (3 /* @p__linq__0 */ IS NOT NULL)
Just the 4th query was fully optimized. But I was expecting that #1, #2, #5 and #6 was too
Closed Feb 18, 2014 at 8:29 PM by maumar
verified, closing

comments

emilcicos wrote Feb 13, 2014 at 10:26 PM

Which version of EF are you using? I am getting results that are a bit different:

1

SELECT
1 AS [C1]
FROM [dbo].[A] AS [Extent1]
WHERE 3 = [Extent1].[IdFloorNullAble]

2

SELECT
1 AS [C1]
FROM [dbo].[A] AS [Extent1]
WHERE ([Extent1].[IdFloorNullAble] = @p__linq__0) OR (([Extent1].[IdFloorNullAble] IS NULL) AND (@p__linq__0 IS NULL))

3

SELECT
1 AS [C1]
FROM [dbo].[A] AS [Extent1]
WHERE ([Extent1].[IdFloorNullAble] = @p__linq__0) OR (([Extent1].[IdFloorNullAble] IS NULL) AND (@p__linq__0 IS NULL))

4

SELECT
1 AS [C1]
FROM [dbo].[A] AS [Extent1]
WHERE 3 = [Extent1].[IdContactNotNull]

5

SELECT
1 AS [C1]
FROM [dbo].[A] AS [Extent1]
WHERE [Extent1].[IdContactNotNull] = @p__linq__0

6

SELECT
1 AS [C1]
FROM [dbo].[A] AS [Extent1]
WHERE [Extent1].[IdContactNotNull] = @p__linq__0
The case that is not optimized is #2 and that seems to be a bug. The left operand of "x.IdFloorNullAble == a" is nullable so there is probably a conversion of "a" to Nullable<int> that we don't handle correctly (I will see if we can do anything about it at this time).

Case #3 is expected because both operands in "x.IdFloorNullAble == b" are nullable. Note that EF treats variables as input parameters for the query, so the variable type dictates its nullability and not the current value.

fujiy wrote Feb 14, 2014 at 6:57 AM

6.0.2

emilcicos wrote Feb 14, 2014 at 5:58 PM

That makes sense. These optimizations went into 6.1.0 Beta 1.

Also commit 3ce50146d9dbee26f2d1b1243e0b4a3abc5e4042 into the master branch fixes case #2 above, and it is evaluated for including into the release-6.1.0 branch.

emilcicos wrote Feb 14, 2014 at 7:56 PM

The fix for #2 will make it into the 6.1.0 release.
commit b80976cd09d7d5f8b29991e2ec03985424dbcaf3

fujiy wrote Feb 17, 2014 at 5:05 PM

Great! Thx