All and Any methods don't work correctly with null values

[samcook]

If you have a DataAccessObject with a property that can contain nulls (e.g. string, nullable int, nullable bool, etc...), performing an All(x => ...) operation can give incorrect results, as it appears to generate SQL that ignores rows with null values in the field.

e.g.

[DataAccessObject]
public class DbObject : DataAccessObject<int>
{
    [PersistedMember]
    public virtual string Foo { get; set; }
}

Id	Foo
1	hello
2	NULL
3	NULL

this.Model.DbObjects.All(x => x.Foo == "hello").ShouldBe(false);
// actually returns true

this.Model.DbObjects.Where(x => x.Foo == null).All(x => x.Foo == "anything").ShouldBe(false);
// actually returns true

The SQL generated for the first example looks something like:

SELECT
    (
        SELECT CASE
                   WHEN (NOT ((EXISTS
                               (
                                   SELECT "T0"."DbObjectId",
                                          "T0"."Foo"
                                   FROM "DbObject" AS "T0"
                                   WHERE NOT ((("T0"."Foo") = ('hello')))
                               )
                              )
                             )
                        ) THEN
            (1)
                   ELSE
            (0)
               END AS "EXISTS_COL"
    ) AS "value";

I think the problem is that the WHERE clause in the inner query is causing the rows with NULLs to be omitted.

TLDR:

• If there are any nulls matching the All predicate, they seem to be ignored and the result is based on the non null values.
• If there are only nulls matching the All predicate, the result is always true.

[samcook]

Also affects Any with !=

this.Model.DbObjects.Any(x => x.Foo != "hello").ShouldBe(true);
// actually returns false

SELECT
    (
        SELECT CASE
                   WHEN ((EXISTS
                          (
                              SELECT "T0"."DbObjectId",
                                     "T0"."Foo"
                              FROM "DbObject" AS "T0"
                              WHERE (("T0"."Foo") <> ('hello'))
                          )
                         )
                        ) THEN
            (1)
                   ELSE
            (0)
               END AS "EXISTS_COL"
    ) AS "value";

[samcook]

For reference, this is what EntityFramework 6 generates:

db.DbObjects.All(x => x.Foo == "hello"); // returns false

SELECT CASE
           WHEN (NOT EXISTS
                     (
                         SELECT 1 AS [C1]
                         FROM [dbo].[DbObjects] AS [Extent1]
                         WHERE (NOT (
                                        (N'hello' = [Extent1].[Foo])
                                        AND ([Extent1].[Foo] IS NOT NULL)
                                    )
                               )
                               OR (CASE
                                       WHEN (N'hello' = [Extent1].[Foo]) THEN
                                           CAST(1 AS BIT)
                                       WHEN (NOT (
                                                     (N'hello' = [Extent1].[Foo])
                                                     AND ([Extent1].[Foo] IS NOT NULL)
                                                 )
                                            ) THEN
                                           CAST(0 AS BIT)
                                   END IS NULL
                                  )
                     )
                ) THEN
               CAST(1 AS BIT)
           ELSE
               CAST(0 AS BIT)
       END AS [C1]
FROM
(SELECT 1 AS X) AS [SingleRowTable1];

db.DbObjects.Any(x => x.Foo != "hello"); // returns true

SELECT CASE
           WHEN (EXISTS
                 (
                     SELECT 1 AS [C1]
                     FROM [dbo].[DbObjects] AS [Extent1]
                     WHERE NOT (
                                   (N'hello' = [Extent1].[Foo])
                                   AND ([Extent1].[Foo] IS NOT NULL)
                               )
                 )
                ) THEN
               CAST(1 AS BIT)
           ELSE
               CAST(0 AS BIT)
       END AS [C1]
FROM
(SELECT 1 AS X) AS [SingleRowTable1];

[tumtumtum]

Could you add a unit test?

[samcook]

Will do

[samcook]

Unsurprisingly, the same issue can also apply to the predicates for Where, Count, etc...

[tumtumtum]

TLDR but is this is an issue with SQL server only or all providers?

[samcook]

Affects both SQL server and sqlite, haven't tried mysql or postgres

[tumtumtum]

Yeah comparing with null is undefined so not(undefined) isn't true.

I have to have a think about where else this affects could affect the framework because LINQ semantics for NULL is different.