Invalid SQL generated when using Null Coalescing Operator on bool for the SQLServer provider
- Dominant language
- C#
- Stars
- 14.8k
- Forks
- 3.4k
- PR merge metrics
- PR metrics pending
Description
Given the following
```C#
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 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()
.HasOne(p => p.Registration)
.WithOne()
.HasForeignKey("vehicle_id")
.IsRequired();
}
}
```
When I execute the following query:
```C#
db.Vehicles.Where(p => !p.Registration.Approved ?? true);
```
The generated sql is wrong:
```sql
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:
```sql
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:
```C#
db.Vehicles.Where(p => p.Registration.Approved == null || !p.Registration.Approved.Value));
```
Where the following good sql is produced:
```sql
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)
Contributor guide
Assessment
This issue has not been assessed yet.