Query: null semantics not applied on subquery.Contains(null)

[maumar]

Query:

context.LevelOne.Select(t => t.OneToOne_Optional_FK1).Contains(null);

this should return true, however we generate the following sql:

SELECT CASE
    WHEN NULL IN (
        SELECT [l0].[Id]
        FROM [LevelOne] AS [l]
        LEFT JOIN [LevelTwo] AS [l0] ON [l].[Id] = [l0].[Level1_Optional_Id]
    ) THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END

which evaluates to false

We should perhaps convert to

context.LevelOne.Select(t => t.OneToOne_Optional_FK1).Any(x => x == null);

which works correctly

[maumar]

We can do the transformation as part of pre-processing (AllAnyToContainsRewritingExpressionVisitor). However the problem is that we ONLY need to do this if the item argument of the Contains is nullable. We can do it always, but that results in uglier SQL, e.g.

old:

@__id_0='1'

SELECT [e].[Id], [e].[Name]
FROM [Entities] AS [e]
WHERE [e].[Id] IN (
    SELECT [e0].[Id]
    FROM [Entities] AS [e0]
    WHERE [e0].[Id] = @__id_0
)

new:

@__id_0='1'

SELECT [e].[Id], [e].[Name]
FROM [Entities] AS [e]
WHERE EXISTS (
    SELECT 1
    FROM [Entities] AS [e0]
    WHERE ([e0].[Id] = @__id_0) AND ([e0].[Id] = [e].[Id]))

Alternatively we could recognize the pattern during null semantics rewrite, when we know the nullability of the item. However that would require to synthesize a new SelectExpression, so not sure if its a good idea. @smitpatel thoughts?

[smitpatel]

Run query perf numbers on both the generated SQL, then we can see which SQL is more beneficial and thing about it.

[maumar]

fixed in b84eec1