ctx.Set<Entity>().Select(e => !(e.NullableInt <= 1) ? 0 : 1)
returns 1 for rows where NullableInt is NULL; C# gives 0. Generated SQL: CASE WHEN NOT ("e"."NullableInt" <= 1) THEN 0 ELSE 1 END. Reproduces on 10.0 and main (SQLite and SQL Server).
Cause: the nullability processor correctly wraps the comparison as CASE WHEN x THEN FALSE ELSE TRUE END, but the CASE flattening in SqlExpressionFactory.Case (#34175) turns WHEN (CASE WHEN x THEN FALSE ELSE TRUE END) THEN y into WHEN NOT x THEN y, which is NULL rather than TRUE when x is NULL.
Proposed fix: with a single WHEN clause, invert instead (CASE WHEN x THEN z ELSE y END); otherwise keep the nested CASE. I have a PR ready with a spec test.
Update, here is how it breaks (.NET Fiddle):
using Microsoft.EntityFrameworkCore;
using System.Collections.Immutable;
using System.Linq;
using System;
using System.Linq.Expressions;
using var context = new MyDbContext();
await context.Database.EnsureDeletedAsync();
await context.Database.EnsureCreatedAsync();
Expression<Func<TestRow, int?>> expr = e => !(e.Col1 <= 1) ? 0 : 1;
// Through EF Core
foreach (var db in (await context.TestRows.Select(expr).ToListAsync()))
{
Console.WriteLine(db);
}
Console.WriteLine("--------------");
// Through LINQ
foreach (var db in TestRow.CreateTestRows().Select(expr.Compile()))
{
Console.WriteLine(db);
}
public class MyDbContext : DbContext
{
public DbSet<TestRow> TestRows => Set<TestRow>();
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<TestRow>().Property(x => x.Id).ValueGeneratedNever();
modelBuilder.Entity<TestRow>().HasData(TestRow.CreateTestRows());
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
=> optionsBuilder
.UseSqlServer($@"Server=(localdb)\mssqllocaldb;Database={GetType().Assembly.GetName().Name};Trusted_Connection=True")/*.LogTo(Console.WriteLine)*/;
}
public record TestRow(int Id, int? Col1)
{
public static ImmutableArray<TestRow> CreateTestRows() => [new(2, null)];
}
returns 1 for rows where
NullableIntis NULL; C# gives 0. Generated SQL:CASE WHEN NOT ("e"."NullableInt" <= 1) THEN 0 ELSE 1 END. Reproduces on 10.0 and main (SQLite and SQL Server).Cause: the nullability processor correctly wraps the comparison as
CASE WHEN x THEN FALSE ELSE TRUE END, but the CASE flattening inSqlExpressionFactory.Case(#34175) turnsWHEN (CASE WHEN x THEN FALSE ELSE TRUE END) THEN yintoWHEN NOT x THEN y, which is NULL rather than TRUE whenxis NULL.Proposed fix: with a single WHEN clause, invert instead (
CASE WHEN x THEN z ELSE y END); otherwise keep the nested CASE. I have a PR ready with a spec test.Update, here is how it breaks (.NET Fiddle):