EF Core 9 no longer applies COALESCE in SQL translation for DefaultIfEmpty() which causing an InvalidOperationException

[akarboush]

Bug description

In EF Core 8, queries using DefaultIfEmpty() on value types were automatically translated to include a COALESCE function in the generated SQL:

// LINQ query
from account in dbContext.Accounts
from balance in account.Transactions
    .Where(x => x.AccountId > 500)
    .Take(1)
    .OrderBy(x => true)
    .Select(x => x.Balance)
    .DefaultIfEmpty()
select balance;

-- EF Core 8 SQL translation
SELECT COALESCE([t0].[Balance], 0.0)
FROM [Accounts] AS [a]
OUTER APPLY (
    SELECT TOP(1) [t].[Balance]
    FROM [Transactions] AS [t]
    WHERE [a].[AccountId] = [t].[AccountId] AND [t].[AccountId] > 500
) AS [t0]

In EF Core 9, this translation behavior has changed. The COALESCE is no longer applied:

-- EF Core 9 SQL translation
SELECT [t0].[Balance]
FROM [Accounts] AS [a]
OUTER APPLY (
    SELECT TOP(1) [t].[Balance]
    FROM [Transactions] AS [t]
    WHERE [a].[AccountId] = [t].[AccountId] AND [t].[AccountId] > 500
) AS [t0]

Without the COALESCE, NULL values can be returned, causing InvalidOperationException: Nullable object must have a value when materializing to non-nullable types.

The workaround is to explicitly handle nullability in the LINQ query by casting to a nullable type and using the null-coalescing operator:

from account in dbContext.Accounts
from balance in account.Transactions
    .Where(x => x.AccountId > 500)
    .Take(1)
    .OrderBy(x => true)
    .Select(x => (decimal?)x.Balance) // Cast to nullable
    .DefaultIfEmpty()
select balance ?? 0m; // Handle null

Your code

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using System.ComponentModel.DataAnnotations.Schema;
using System.Text.Json;


using var dbContext = new PlayGroundEfCoreContext();

dbContext.Database.EnsureCreated();

/*
dbContext.Add(new Account()
{
    Transactions = new List<Transaction>()
    {
        new Transaction()
        {
            Balance = 100
        }
    }
});

dbContext.SaveChanges();
*/

var balances = (
    from account in dbContext.Accounts

    from balance in account.Transactions
        .Where(x => x.AccountId > 500)
        .Take(1)
        .OrderBy(x => true)
        .Select(x => x.Balance)
        .DefaultIfEmpty()

    select balance).ToList();


Console.WriteLine(JsonSerializer.Serialize(balances));


public class PlayGroundEfCoreContext : DbContext
{
    public DbSet<Account> Accounts { get; set; }
    public DbSet<Transaction> Transactions { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseSqlite("Data Source=test.db")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Account>()
            .HasMany(a => a.Transactions)
            .WithOne(t => t.Account)
            .HasForeignKey(t => t.AccountId);
    }
}

[Table("Accounts")]
public class Account
{
    public int AccountId { get; set; }
    public ICollection<Transaction> Transactions { get; set; }
}

[Table("Transactions")]
public class Transaction
{
    public int TransactionId { get; set; }
    public int AccountId { get; set; }
    public decimal Balance { get; set; }
    public Account Account { get; set; }
}

Stack traces

System.InvalidOperationException
  HResult=0x80131509
  Message=Nullable object must have a value.
  Source=System.Private.CoreLib
  StackTrace:
   at System.ThrowHelper.ThrowInvalidOperationException_InvalidOperation_NoValue()
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Program.<<Main>$>d__0.MoveNext() in

Verbose output

EF Core version

9.0.4

Database provider

SqlServer & Sqlite

Target framework

No response

Operating system

No response

IDE

No response

[ranma42]

@akarboush I can reproduce the issue on SqlServer, but on Sqlite I hit a different exception when running your code:

System.InvalidOperationException:
Translating this query requires the SQL APPLY operation, which is not supported on SQLite.

[akarboush]

@akarboush I can reproduce the issue on SqlServer, but on Sqlite I hit a different exception when running your code:

System.InvalidOperationException:
Translating this query requires the SQL APPLY operation, which is not supported on SQLite.

@ranma42 originally, I had the problem with SQL Server, but when I wrote the example, it also failed with SQLite, though it might be a different exception

[roji]

Confirmed regression in 9.0.

Minimal repro

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

context.Accounts.Add(new()
{
    Transactions = [new()]
});

await context.SaveChangesAsync();

_ = await (
    from account in context.Accounts
    from balance in account.Transactions
        .Where(x => x.AccountId > 500)
        .Take(1)
        .OrderBy(x => true)
        .Select(x => x.Balance)
        .DefaultIfEmpty()
    select balance).ToListAsync();

public class PlayGroundEfCoreContext : DbContext
{
    public DbSet<Account> Accounts { get; set; }
    public DbSet<Transaction> Transactions { 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 Account
{
    public int AccountId { get; set; }
    public ICollection<Transaction> Transactions { get; set; }
}

public class Transaction
{
    public int TransactionId { get; set; }
    public int AccountId { get; set; }
    public decimal Balance { get; set; }
    public Account Account { get; set; }
}

[roji]

The regression was triggered by #34002, which optimizes away COALESCE when it is deemed not needed, e.g. COALESCE over a non-nullable column. However, this is not the root cause: the real problem is that the ColumnExpression representing t.Balance in the COALESCE is non-nullable, when it should be nullable (as it's after a DefaultIfEmpty). #34002 only exposed the problem (previously the COALESCE wasn't optimized away and so the bug remained hidden).

Now for why the column is (incorrectly) non-nullable... Before SelectMany translates its selector, it first does a pass; if it detects a DefaultIfEmpty inside it removes it and remembers it saw it - that determines whether it adds a CROSS or OUTER APPLY (code). The problem is, since the DefaultIfEmpty is removed, then when we later get to the Coalesce node, it's non-nullable and so the node is stripped.

We could simply leave the DefaultIfEmpty in the selector, but then that bloats the SQL; there's indeed no need for an additional DefaultIfEmpty within an OUTER APPLY.

Instead, I've submitted #36238, where we replace the DefaultIfEmpty with a FakeDefaultIfEmpty, which makes the projection nullable (necessary to fix preserve to COALESCE on that projection) but does not modify the SQL. This is slightly hacky, but quite self-contained and seems reasonable. An alternative approach would be to set some state flag to vary the translation behavior of DefaultIfEmpty, but I'd like to avoid the complexity of additional visitor state here.