SqlServer never returns `null` for nullable Boolean expressions

[ranma42]

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

An example program that showcases the bug is:

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<Blog> 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<Blog>().HasData(new Blog { BlogId = 1, NullableInt = 0 });
		modelBuilder.Entity<Blog>().HasData(new Blog { BlogId = 2, NullableInt = 1 });
		modelBuilder.Entity<Blog>().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

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

[ranma42]

Some specific bool? expression already work (example: propagating the value of a nullable column). The problem is related to the conversion from Boolean to BIT (it mixes false and NULL values)

[ranma42]

I am investigating the possible translations. Currently the best candidate for an expression x seems to be:

CASE
    WHEN x IS NULL THEN NULL
    WHEN x THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END

The main advantage of this translation is that (as long as appropriate simplifications are performed), x IS NULL can be much simpler than x; in the trivial case in which x is not nullable, the clause would even simplify away completely.