dotnet / dotnet/efcore

SqlServer never returns `null` for nullable Boolean expressions

Open
#34,001 2 comments 0 reactions 0 assignees View on GitHub
area-query consider-for-next-release customer-reported
Dominant language
C#
Stars
14.8k
Forks
3.4k
PR merge metrics
PR metrics pending

Description

The SqlServer provider never returns `null` for `bool?` expressions; instead it returns `false`.

An example program that showcases the bug is:

```C#
using System;
using System.Data;
using System.Linq;
using Microsoft.EntityFrameworkCore;

using var db = new BloggingContext();

var qs = db.Blogs
.Select(x => x.NullableInt > 0)
.ToQueryString();

Console.WriteLine(qs);

public class BloggingContext : DbContext
{
public DbSet Blogs { get; set; }

protected override void OnConfiguring(DbContextOptionsBuilder options)
=> options
.LogTo(Console.WriteLine, Microsoft.Extensions.Logging.LogLevel.Information)
.UseSqlServer();

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity().HasData(new Blog { BlogId = 1, NullableInt = 0 });
modelBuilder.Entity().HasData(new Blog { BlogId = 2, NullableInt = 1 });
modelBuilder.Entity().HasData(new Blog { BlogId = 3, NullableInt = null });
}
}

public class Blog
{
public int BlogId { get; set; }
public int? NullableInt { get; set; }
}
```

The query is translated to
```sql
SELECT CASE
WHEN [b].[NullableInt] > 0 THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END
FROM [Blogs] AS [b]
```
hence the result of the `SELECT` can only be `0` or `1` (this also happens by actually performing the query; in that case it obviously requires a running instance of SqlServer).

### Include provider and version information

EF Core version: 8.0.6
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 8.0
Operating system: Linux (/WSL)
IDE: Visual Studio Code 1.89.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.