Unexpected semantics if .All() predicate evaluates to NULL for some rows?

[georg-jung]

I'm not sure whether the following sematics of .All() are by design as EF Core's behaviour differs from LINQs behaviour:

E.g. SomeCollection.All(x => x.NullableInt > 2) returns true in EF Core and false in LINQ if any of the NullableInt values is null, if all non-null values satisfy the condition. If all NullableInt values are null, in EF Core's semantics this also means SomeCollection.All(x => x.NullableInt > 2) == true == SomeCollection.All(x => x.NullableInt <= 2).

Code

using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using System.Text.Json;

const string ConnectionString = @"Data Source=InMemorySample;Mode=Memory;Cache=Shared";

await using var keepOpenCon = new SqliteConnection(ConnectionString);
await keepOpenCon.OpenAsync();

await using var ctx = new ReproContext(ConnectionString);
await ctx.Database.EnsureDeletedAsync();
await ctx.Database.EnsureCreatedAsync();

Console.WriteLine(ctx.Database.GenerateCreateScript());
Console.WriteLine("----");

var lst = new List<Blog> {
    new() { Name = "FooBlog 1", Rating = null, IsNice = null },
    new() { Name = "FooBlog 2", Rating = 2, IsNice = true },
    new() { Name = "FooBlog 3", Rating = null, IsNice = null },
    new() { Name = "FooBlog 4", Rating = 2, IsNice = true },
};
ctx.Blogs.AddRange(lst);
await ctx.SaveChangesAsync();
Console.WriteLine(JsonSerializer.Serialize(lst));
Console.WriteLine("----");

Console.WriteLine("-- Expected NULL Propagation with int? and == --");
var resp = await ctx.Blogs.AllAsync(b => b.Rating == 2);
Console.WriteLine(JsonSerializer.Serialize(resp));

Console.WriteLine();
Console.WriteLine("-- Unexpected NULL Propagation with int? and > --");
var resp1 = await ctx.Blogs.AllAsync(b => b.Rating >= 1); // wouldn't one expect this to be false, no matter which variant of <, <=, >, >= is used with whatever value?
Console.WriteLine(JsonSerializer.Serialize(resp1));

Console.WriteLine();
Console.WriteLine("-- Local LINQ Evaluation --");
Console.WriteLine(JsonSerializer.Serialize(lst.All(b => b.Rating >= 1)));


Console.WriteLine();
Console.WriteLine("-- Manual Workaround NULL Check in Queryable --");
var resp2 = await ctx.Blogs.AllAsync(b => b.Rating.HasValue && b.Rating >= 1);
Console.WriteLine(JsonSerializer.Serialize(resp2));

Console.WriteLine();
Console.WriteLine("-- Unexpected NULL Propagation with bool? --");
var resp3 = await ctx.Blogs.AllAsync(b => b.IsNice!.Value); // okay, I used dammit here, but maybe one would expect this to be the same as below?
Console.WriteLine(JsonSerializer.Serialize(resp3));

Console.WriteLine();
Console.WriteLine("-- Expected NULL Propagation with bool? --");
var resp4 = await ctx.Blogs.AllAsync(b => b.IsNice == true);
Console.WriteLine(JsonSerializer.Serialize(resp4));


public class ReproContext(string ConnectionString) : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder.UseSqlite(ConnectionString).LogTo(Console.WriteLine, Microsoft.Extensions.Logging.LogLevel.Information, Microsoft.EntityFrameworkCore.Diagnostics.DbContextLoggerOptions.None);
}

public class Blog
{
    public int Id { get; set; }
    public required string Name { get; set; }
    public required int? Rating { get; set; }
    public required bool? IsNice { get; set; }
}

<Project Sdk="Microsoft.NET.Sdk">
	<PropertyGroup>
		<OutputType>Exe</OutputType>
		<TargetFramework>net8.0</TargetFramework>
		<ImplicitUsings>enable</ImplicitUsings>
		<Nullable>enable</Nullable>
	</PropertyGroup>

	<ItemGroup>
		<PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="8.0.7" />
	</ItemGroup>
</Project>

Output

...
-- Expected NULL Propagation with int? and == --
Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT NOT EXISTS (
    SELECT 1
    FROM "Blogs" AS "b"
    WHERE "b"."Rating" <> 2 OR "b"."Rating" IS NULL)
false

-- Unexpected NULL Propagation with int? and > --
Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT NOT EXISTS (
    SELECT 1
    FROM "Blogs" AS "b"
    WHERE NOT ("b"."Rating" >= 1))
true

-- Local LINQ Evaluation --
false

-- Manual Workaround NULL Check in Queryable --
Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT NOT EXISTS (
    SELECT 1
    FROM "Blogs" AS "b"
    WHERE "b"."Rating" IS NULL OR "b"."Rating" < 1)
false

-- Unexpected NULL Propagation with bool? --
Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT NOT EXISTS (
    SELECT 1
    FROM "Blogs" AS "b"
    WHERE NOT ("b"."IsNice"))
true

-- Expected NULL Propagation with bool? --
Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT NOT EXISTS (
    SELECT 1
    FROM "Blogs" AS "b"
    WHERE "b"."IsNice" = 0 OR "b"."IsNice" IS NULL)
false

After reading Query null semantics, I wasn't sure if the above behaviour is expected or if anything of that is unexpected/a bug?

[roji]

At first glance looks like a null semantics bug? /cc @maumar @ranma42

[ranma42]

I believe that the int comparison has been fixed by #33757

Using Microsoft.EntityFrameworkCore.Sqlite 9.0.0-preview.6.24327.4 I get the following output:

Executed DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT COUNT(*) FROM "sqlite_master" WHERE "type" = 'table' AND "rootpage" IS NOT NULL;
Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE "Blogs" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_Blogs" PRIMARY KEY AUTOINCREMENT,
    "Name" TEXT NOT NULL,
    "Rating" INTEGER NULL,
    "IsNice" INTEGER NULL
);
CREATE TABLE "Blogs" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_Blogs" PRIMARY KEY AUTOINCREMENT,
    "Name" TEXT NOT NULL,
    "Rating" INTEGER NULL,
    "IsNice" INTEGER NULL
);



----
Executed DbCommand (2ms) [Parameters=[@p0='?' (DbType = Boolean), @p1='?' (Size = 9), @p2='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
INSERT INTO "Blogs" ("IsNice", "Name", "Rating")
VALUES (@p0, @p1, @p2)
RETURNING "Id";
Executed DbCommand (0ms) [Parameters=[@p0='?' (DbType = Boolean), @p1='?' (Size = 9), @p2='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
INSERT INTO "Blogs" ("IsNice", "Name", "Rating")
VALUES (@p0, @p1, @p2)
RETURNING "Id";
Executed DbCommand (0ms) [Parameters=[@p0='?' (DbType = Boolean), @p1='?' (Size = 9), @p2='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
INSERT INTO "Blogs" ("IsNice", "Name", "Rating")
VALUES (@p0, @p1, @p2)
RETURNING "Id";
Executed DbCommand (0ms) [Parameters=[@p0='?' (DbType = Boolean), @p1='?' (Size = 9), @p2='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
INSERT INTO "Blogs" ("IsNice", "Name", "Rating")
VALUES (@p0, @p1, @p2)
RETURNING "Id";
[{"Id":1,"Name":"FooBlog 1","Rating":null,"IsNice":null},{"Id":2,"Name":"FooBlog 2","Rating":2,"IsNice":true},{"Id":3,"Name":"FooBlog 3","Rating":null,"IsNice":null},{"Id":4,"Name":"FooBlog 4","Rating":2,"IsNice":true}]
----
-- Expected NULL Propagation with int? and == --
Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT NOT EXISTS (
    SELECT 1
    FROM "Blogs" AS "b"
    WHERE "b"."Rating" <> 2 OR "b"."Rating" IS NULL)
false

-- Unexpected NULL Propagation with int? and > --
Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT NOT EXISTS (
    SELECT 1
    FROM "Blogs" AS "b"
    WHERE CASE
        WHEN "b"."Rating" >= 1 THEN 0
        ELSE 1
    END)
false

-- Local LINQ Evaluation --
false

-- Manual Workaround NULL Check in Queryable --
Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT NOT EXISTS (
    SELECT 1
    FROM "Blogs" AS "b"
    WHERE "b"."Rating" IS NULL OR "b"."Rating" < 1)
false

-- Unexpected NULL Propagation with bool? --
Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT NOT EXISTS (
    SELECT 1
    FROM "Blogs" AS "b"
    WHERE NOT ("b"."IsNice"))
true

-- Expected NULL Propagation with bool? --
Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT NOT EXISTS (
    SELECT 1
    FROM "Blogs" AS "b"
    WHERE "b"."IsNice" = 0 OR "b"."IsNice" IS NULL)
false

[roji]

Thanks @ranma42, makes sense. @georg-jung can you give preview.6 a try and confirm that the issue no longer happens there?

[ranma42]

I am unsure what the behavior for var resp3 = await ctx.Blogs.AllAsync(b => b.IsNice!.Value); should be as it is basically doing an All on bool?😕
I guess that a reasonable generalization for that is "All" is the aggregation form of AND, but this is not sufficient to completely determine the expected behavior:

1. a direct application of AND to aggregate the IsNice values would lead to NULL AND TRUE AND NULL AND TRUE → NULL
2. most SQL aggregation function ignore NULL values, in which case the result of the aggregation would be TRUE AND TRUE → TRUE
3. another option would be to consider NULL as "falsy", in which case the correct result would be FALSE.

OTOH I thinks one might expect one of the following cases:

• the result of the SQL evaluation matches the one of C# (although that is not always the case in EFCore); this is somewhat ill-posed, given that the typing of the C# aggregation does not allow null
• the result of the SQL evaluation matches the "equivalent" of All, which AFAICT could be every/bool_and in postgresql (I am unable to find another "directly matching" aggregation operator, but one could probably obtain it trivially from MAX/MIN in Sqlite); this function seems to behave as described in case 2 (you can try it out https://dbfiddle.uk/0LFchg6F )

The current behavior in EFCore is the second scenario, which matches every in postgresql; I am not sure if this is the expected behavior, but it does not strike me as "obviously wrong" (well, I would not have been able to tell the result of ctx.Blogs.AllAsync(b => b.IsNice!.Value) nor of every("IsNice") without trying them out 😅, but I am neither an EFCore expert nor a SQL one)

I am quite happy that var resp4 = await ctx.Blogs.AllAsync(b => b.IsNice == true); is more explicit in what the user expects (aka scenario 3) and that it works as intended 😉.

[georg-jung]

Thank you for the swift and on point responses!

I can confirm that this is fixed with 9.0.0-preview.6.24327.4. I originally stumbled up on this in the context of a somewhat more complex query. Thus, I thought I'd give some more nested queries a try too. I can report that all these also work as I expected now on the 9.x version (they don't on 8.0.x).

using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using System.Text.Json;

const string ConnectionString = @"Data Source=InMemorySample;Mode=Memory;Cache=Shared";

await using var keepOpenCon = new SqliteConnection(ConnectionString);
await keepOpenCon.OpenAsync();

await using var ctx = new ReproContext(ConnectionString);
await ctx.Database.EnsureDeletedAsync();
await ctx.Database.EnsureCreatedAsync();

Console.WriteLine(ctx.Database.GenerateCreateScript());
Console.WriteLine("----");

var lst = new List<Blog> {
    new() { Name = "FooBlog 1", Ratings = [ new() { Author = "Peter", Stars = 5, Created = DateTime.Now } ]},
    new() { Name = "FooBlog 2", Ratings = []},
    new() { Name = "FooBlog 3", Ratings = [ new() { Author = "John", Stars = 3, Created = DateTime.Now }, new() { Author = "Alice", Stars = 4, Created = DateTime.Now } ]},
    new() { Name = "FooBlog 4", Ratings = [ new() { Author = "Sarah", Stars = 2, Created = DateTime.Now } ]},
};
ctx.Blogs.AddRange(lst);
await ctx.SaveChangesAsync();
Console.WriteLine(JsonSerializer.Serialize(lst));
Console.WriteLine("----");

Console.WriteLine("-- Subquery aggregate NULL propagation 1 --");
var resp = await ctx.Blogs.AllAsync(b => b.Ratings.Min(r => r.Stars) > 1);
Console.WriteLine(JsonSerializer.Serialize(resp));

Console.WriteLine("-- Subquery aggregate NULL propagation 2 --");
var resp2 = await ctx.Blogs.AllAsync(b => b.Ratings.Max(r => r.Created) > DateTime.Now.AddDays(-1));
Console.WriteLine(JsonSerializer.Serialize(resp2));


public class ReproContext(string ConnectionString) : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    public DbSet<Rating> Ratings { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder.UseSqlite(ConnectionString).LogTo(Console.WriteLine, Microsoft.Extensions.Logging.LogLevel.Information, Microsoft.EntityFrameworkCore.Diagnostics.DbContextLoggerOptions.None);
}

public class Blog
{
    public int Id { get; set; }
    public required string Name { get; set; }
    public List<Rating> Ratings { get; set; } = [];
}

public class Rating
{
    public int Id { get; set; }
    public required string Author { get; set; }
    public required int? Stars { get; set; }
    public DateTime Created { get; set; } // sqlite support for DateTimeOffset seems more limited
}

Thanks for getting this right and the overall great quality work as always!

---

the result of the SQL evaluation matches the "equivalent" of All, which AFAICT could be every/bool_and in postgresql (I am unable to find another "directly matching" aggregation operator, but one could probably obtain it trivially from MAX/MIN in Sqlite); this function seems to behave as described in case 2 (you can try it out https://dbfiddle.uk/0LFchg6F )

The more recent postgres docs for v16 even state this clearly:

Returns true if all non-null input values are true, otherwise false.

---

I agree that it's hard to tell which variant is right. Probably it makes most sense for it to behave in line with the rest of EF Core in similar situations but I don't think I know about it (and the reasoning behind) well enough.

My first thought from a C# perspective was that NullableBool!.Value would behave as NullableBool == true, given in C# it either would do so or would throw - but it would never return true if the other returns false. On the other hand the SQL-matching behaviour makes sense too. One could argue that this way we are able to express either one or the other. Is there any more general idea in EF to rather behave like C# or like SQL? In my experience it's rather "behave like C# would", but I'm no expert for the details, edge cases and reasoning behind.

[roji]

most SQL aggregation function ignore NULL values, in which case the result of the aggregation would be TRUE AND TRUE → TRUE

It's worth noting that this behavior also holds in (at least some) .NET aggregate contexts. For example, I recently did some work on the Max() operator, which is defined over nullable numeric types (e.g. here), and those variants also ignore nulls (in the sense that they only return null if all arguments are null (or the argument list is empty)).

In general, wherever a query can be evaluated in-memory (LINQ-to-Objects), we strive to match that behavior in EF (there are exceptions to this as well, where that behavior simply doesn't correspond to the database's natural behavior, and/or would cause performance issues). We specifically don't mimic the database's behavior around nulls (3-value logic), and specifically add SQL to compensate for the behavior difference and produce .NET behavior. The UseRelationalNulls() opt-in exists specifically to allow users to avoid those compensations and have EF translate their LINQ query more directly to SQL.

Now, as @ranma42 pointed out, this is one of those scenarios where we don't have an in-memory behavior to emulate - there's no All() operator over nullable bools, and LINQ-to-Objects throws for Nullable<int>.Value for nulls. So I think my preference here is what we currently do, i.e. follow the Max() behavior of ignoring nulls. Given that as @ranma42 pointed out, most SQL aggregate functions ignore nulls (e.g. MAX()), that could even make sense from that perspective (PG's bool_and seems exceptional from that perspective, and shouldn't necessarily inform general decisions for other databases too). Finally, there's the advantage of this behavior being our current one, so no need to change anything ;)

(BTW we could also interpret UseRelationalNulls() as a signal to switch to the more pure 3-value logic of returning null if any argument is null, but I'm not even sure that makes sense given that most SQL aggregate functions don't have this behavior (e.g. MAX())).

How does that all sound?

[georg-jung]

I think that makes perfect sense, thank you for the thoughts and insights!

I thought the behaviour was exceptional because, from a LINQ-to-Objects perspective, everything that flows into All is a bool and thus needs to be true or false and I felt null was falsey. Instead applying the more general view of aggregate functions makes sense though.

Isn't even PG's bool_and not exceptional but in line with EF's current behaviour, as it also ignores nulls?

[ranma42]

Isn't even PG's bool_and not exceptional but in line with EF's current behaviour, as it also ignores nulls?

Yes, bool_and (just like its alias every) match the current behavior; a similar result would be obtained in sqlite using min (sqlite uses 0 for FALSE, 1 for TRUE; just like the other SQL engines, it ignores NULLs in most aggregation functions).

Off the top of my head, COUNT is the only aggregation that I remember taking NULLs into account.

[roji]

You're right, bool_and indeed also ignores nulls - the description in the docs seems a bit misleading ("true if all input values are true, otherwise false").

[roji]

Duplicate of #33757