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 at 8:29 PM by maumar
verified, closing

comments

emilcicos wrote Feb 13 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 at 6:57 AM

6.0.2

emilcicos wrote Feb 14 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 at 7:56 PM

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

fujiy wrote Feb 17 at 5:05 PM

Great! Thx