UseRelationalNulls() works incorrectly when comparing nullable bools

[badrshs]

Description

After upgrading to .NET 9 and EF Core, I noticed a change in SQL translation for Boolean comparisons. Specifically, EF Core now uses bitwise operations (~ and ^) instead of a CASE statement. This leads to incorrect results when either operand in the comparison is NULL.

Expected Behavior

EF Core should generate SQL that correctly handles NULL values in Boolean comparisons, as was the case in earlier versions, where it used a CASE statement.

Example of the previous SQL generation:

SELECT CASE
    WHEN [p].[PayeeId] = [p].[Payerd] THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END AS [IsPayerSameAsPayee]

Actual Behavior

After upgrading, EF Core generates SQL using bitwise operations. If either operand is NULL, the result of the operation is NULL, causing the Boolean field in the application to have unexpected values.

Example of the new SQL generation:

SELECT ~CAST([p].[PayeeId] ^ [p].[Payerd] AS bit) AS [IsPayerSameAsPayee]

This behavior causes IsPayerSameAsPayee to be NULL instead of TRUE or FALSE.

Steps to Reproduce

Code

Here are two examples that reproduce the issue:

1. Boolean Comparison Between Two Fields

public Task<PaymentReceiptDataModel> GetPaymentReceiptModelAsync(long paymentId) =>
    UnitOfWork.Query<Payment>(p => p.Id == paymentId)
        .Select(payment => new PaymentReceiptDataModel
        {
            IsPayerSameAsPayee = payment.PayeeId== payment.Payerd, // PayeeId or Payerd are nullable here
            //... Some others attributes..
        })
        .FirstOrDefaultAsync();

2. Boolean Comparison with Enum

public Task<PlanDataModel> GetInstallmentPlanStatusAsync(long paymentItemId) =>
    UnitOfWork.Query<PaymentItem>(p => p.Id == paymentItemId)
        .Select(paymentItem => new PlanDataModel
        {
            IsActive= paymentItem.SomeObjectThatCanBeNullable.Status== PlanStatus.Active,
        })
        .FirstOrDefaultAsync();

SQL Translation Before Upgrade ( was returning true or false always )

SELECT CASE
    WHEN [p].[PayeeId] = [p].[Payerd] THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END AS [IsPayerSameAsPayee]
FROM [Payment].[Payment] AS [p]
WHERE [p].[Id] = @paymentId

SQL Translation After Upgrade ( is returning null when either operand in the comparison is NULL )

SELECT ~CAST([p].[PayeeId] ^ [p].[Payerd] AS bit) AS [IsPayerSameAsPayee]
FROM [Payment].[Payment] AS [p]
WHERE [p].[Id] = @paymentId

Environment Information

EF Core version: EF Core for .NET 9
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 9.0
Operating system: Windows 11
IDE: Visual Studio 2022 17.8

Stack Trace

The issue causes the application to throw a System.InvalidOperationException due to the mismatch between the SQL result and the expected C# data type. Since IsPayerSameAsPayee is a non-nullable bool, but the SQL translation can return NULL when either operand in the comparison is NULL, EF Core attempts to assign the NULL result to the bool property, resulting in the following exception:

System.InvalidOperationException: Nullable object must have a value.

This occurs because C# does not allow a null value to be assigned to a bool without explicitly handling it as a nullable type.

Additional Notes

This change in SQL translation does not appear in the breaking change documentation for EF Core, and after upgrading, lots of stuff started to fail a part , so I'm expecting it's a bug now

[roji]

@badrshs we need a minimal repro here from you.

IIRC the translation using bitwise XOR kicks in when both sides are known to be non-nullable; when one of them is nullable, the previous CASE/WHEN translation is supposed to be generated. Please see the below minimal code sample that shows this; can you tweak it to show it generating the bitwise operation?

Note that if your database actually does contain NULLs, then your EF model must reflect that: if you're telling EF that the property is non-nullable but your database column does contain NULLs, that's a bug in your program.

/cc @ranma42

[badrshs]

Thanks @roji , My EF model do matches the database schema exactly,

[roji]

Then as I wrote above, I can't reproduce the problem - please below for a minimal console program that tries to recreate the issue, but generates the following SQL:

SELECT CASE
    WHEN ([p].[PayeeId] = [p].[PayerId] AND [p].[PayeeId] IS NOT NULL AND [p].[PayerId] IS NOT NULL) OR ([p].[PayeeId] IS NULL AND [p].[PayerId] IS NULL) THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END
FROM [Payments] AS [p]

Can you please tweak the below to show your bug?

(this, by the way, is why we ask that all issues to include a minimal repro, rather than some snippets/screenshots)

Attempted repro

await using var context = new BlogContext();
await context.Database.EnsureDeletedAsync();
await context.Database.EnsureCreatedAsync();

_ = await context.Payments.Select(p => p.PayeeId == p.PayerId).ToListAsync();

public class BlogContext : DbContext
{
    public DbSet<Payment> Payments { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer("Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0;Encrypt=false")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();
}

public class Payment
{
    public int Id { get; set; }
    public string Name { get; set; }

    public long? PayeeId { get; set; }
    public long? PayerId { get; set; }
}

[ranma42]

I managed to emit the queries seen by @badrshs with the following snippet.

// @nuget: Microsoft.EntityFrameworkCore.SqlServer -Version 9.0.0

using System;
using System.Data;
using System.Linq;
using Microsoft.EntityFrameworkCore;

using var context = new BlogContext();

var qs = context.Payments.Select(p => p.PayeeId == p.PayerId).ToQueryString();

Console.WriteLine(qs);

public class BlogContext : DbContext
{
    public DbSet<Payment> Payments { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer("Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0;Encrypt=false",
				o => o.UseRelationalNulls(true)
			)
            .LogTo(Console.WriteLine, Microsoft.Extensions.Logging.LogLevel.Information)
            .EnableSensitiveDataLogging();
}

public class Payment
{
    public int Id { get; set; }
    public string Name { get; set; }

    public long? PayeeId { get; set; }
    public long? PayerId { get; set; }
}

Switching between EFCore 8 and 9 makes this code emit the queries mentioned in the first comment.

Note that this code is explicitly requesting relational NULLs, hence the expected result of the comparison is a bool? (if either operand is NULL, the result is NULL), i.e. the new translation seem to be working as intended, while the old one is affected by #34001 (this is a bug that has been partially tackled in some of my previous PRs, but not yet completely fixed).

⚠ I am not sure this is the issue that @badrshs is hitting; this is just the smallest change I was able to find that could make @roji's code emit queries resembling those in the first post.

[roji]

@ranma42 thanks for looking into this and producing a repro!

@badrshs can you confirm whether you're actually using UseRelationalNulls? If not, we'll need a repro similar to what @ranma42 and I produced above, showing the bug.

If the regression here is only when UseRelationalNulls is activated, this would be significantly lower-priority than otherwise (but of course is still a bug).

[badrshs]

Yep , we found the issue this morning, it was due our test has hidden configuration that use UseRelationalNulls as true . that the exact problem 🙌

[roji]

OK, thanks for confirming @badrshs - now we now what the exact problem is.

@ranma42 let me know if you want to work on a fix; I'm not yet sure whether we'd be backporting it (as UseRelationalNull is generally a very-seldom-used feature).

[ranma42]

@roji if the code is indeed using UseRelationalNull, what is the expected behavior? (aka what would the fix aim for?)

AFAICT the new behavior is consistent with what UseRelationalNull tries to achieve; the full fix would involve getting #34001 fixed, but this specific case already behaves as (I thought was) intended.

[roji]

@ranma42 on second look, I think you are right, and EF is doing the right thing here: when UseRelationalNull is enabled, doing a == b in LINQ for nullable a or nullable b (or both) should indeed return null if either side (or both) is null. In other words, this looks more like an (advertent) bug fix in 9.0.

@badrshs makes sense, or are we missing something? What's your exact reason for turning on UseRelationalNull?

[badrshs]

For us, there was no specific reason; it was just some default configurations in internal packages we use. We changed those configurations to false, and the problem was resolved.

However, my thinking is that it worked before with the same configuration in .NET 8, but after the upgrade, it started failing.

So, this should either be considered a breaking change or a bug, regardless of how UseRelationalNull is intended to work.

or perhaps the issue was supposed to fail in previous versions (,NET 8 ) , and the new update has fixed it as intended, exposing the problem.