efcore
efcore copied to clipboard
dotnet
Invalid SQL generated when using Null Coalescing Operator on bool for the SQLServer provider
Given the following
public class Vehicle
{
public int Id { get; set; }
public VehicleRegistration Registration { get; set; } = default!;
}
public class VehicleRegistration
{
public int Id { get; set; }
public bool? Approved { get; set; }
}
public class MyContext : DbContext
{
public DbSet<Vehicle> Vehicles { get; set; } = default!;
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) => optionsBuilder
//.UseSqlite("Data Source=tst.db;");
.UseSqlServer(@"Server=.;Database=EFCoreIssueNullableBool;Trusted_Connection=True;MultipleActiveResultSets=true");
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<Vehicle>()
.HasOne(p => p.Registration)
.WithOne()
.HasForeignKey<VehicleRegistration>("vehicle_id")
.IsRequired();
}
}
When I execute the following query:
db.Vehicles.Where(p => !p.Registration.Approved ?? true);
The generated sql is wrong:
SELECT [v].[Id]
FROM [Vehicles] AS [v]
LEFT JOIN [VehicleRegistration] AS [v0] ON [v].[Id] = [v0].[vehicle_id]
WHERE COALESCE(CASE
WHEN [v0].[Approved] = CAST(0 AS bit) THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END, CAST(1 AS bit)) = CAST(1 AS bit)
The second argument of the COALESCE function will never be picked even if [v0].[Approved] is null due to the CASE WHEN ... THEN ... ELSE ... END statement always evaluating to a non-null value.
The Sqlite provider generates a good sql that works:
SELECT "v"."Id"
FROM "Vehicles" AS "v"
LEFT JOIN "VehicleRegistration" AS "v0" ON "v"."Id" = "v0"."vehicle_id"
WHERE COALESCE(NOT ("v0"."Approved"), 1)
As a workaround, the query can be written as:
db.Vehicles.Where(p => p.Registration.Approved == null || !p.Registration.Approved.Value));
Where the following good sql is produced:
SELECT [v].[Id]
FROM [Vehicles] AS [v]
LEFT JOIN [VehicleRegistration] AS [v0] ON [v].[Id] = [v0].[vehicle_id]
WHERE ([v0].[Approved] IS NULL) OR ([v0].[Approved] = CAST(0 AS bit))
A working runnable repro is available here: https://github.com/alaatm/EFCoreIssueNullableBool
EF Core version: 6.0.1 Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: (e.g. .NET 6.0.1)
Interestingly, this works too:
db.Vehicles.Where(p => !(p.Registration.Approved ?? false))
generating:
SELECT [v].[Id]
FROM [Vehicles] AS [v]
LEFT JOIN [VehicleRegistration] AS [v0] ON [v].[Id] = [v0].[vehicle_id]
WHERE COALESCE([v0].[Approved], CAST(0 AS bit)) = CAST(0 AS bit)
We don't really process !NULL as possible NULL we try to convert it to 2-value logic before applying ! operator. @maumar will have better idea how null semantics interact with this structure.
I ran into this issue today, almost 2 years and a major version later (v7.0.11). If fixing it is not a priority, the framework should at least fail spectacularly when this syntax is used to avoid insidious, hard to find bugs.
