dotnet / dotnet/efcore

Invalid SQL generated when using Null Coalescing Operator on bool for the SQLServer provider

Open
#27,176 4 comments 0 reactions 0 assignees View on GitHub
area-query customer-reported
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.