Bug description

A correlated SelectMany whose inner source is a GroupBy projection filtered on the grouping key, followed by DefaultIfEmpty(), was translated to a LEFT JOIN up to and including 10.0.0-rc.1. In 10.0.0-rc.2, 10.0.0 and 10.0.12 (the versions I tested) it is translated to OUTER APPLY. Providers without APPLY (SQLite, MariaDB) now throw for a query that worked in 9.0.

Your code

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

using var db = new Ctx();

var sub = from t in db.Transactions
          group t.Amount by t.UserId into g
          select new { g.Key, Amount = g.Sum() };

var query = from u in db.Users
            from t in sub.Where(j => j.Key == u.Id).DefaultIfEmpty()
            select new { u.Id, Amount = (int?)t.Amount ?? 0 };

Console.WriteLine(query.ToQueryString());

public class User { public int Id { get; set; } public string Name { get; set; } }
public class Transaction { public int Id { get; set; } public int UserId { get; set; } public int Amount { get; set; } }

public class Ctx : DbContext
{
    public DbSet<User> Users => Set<User>();
    public DbSet<Transaction> Transactions => Set<Transaction>();
    protected override void OnConfiguring(DbContextOptionsBuilder o) => o.UseSqlite("Data Source=:memory:");
}

Results

EF Core 9.0.9 and 10.0.0-rc.1.25451.107 (SQLite):

SELECT "u"."Id", COALESCE("t0"."Amount", 0) AS "Amount"
FROM "Users" AS "u"
LEFT JOIN (
    SELECT "t"."UserId" AS "Key", COALESCE(SUM("t"."Amount"), 0) AS "Amount"
    FROM "Transactions" AS "t"
    GROUP BY "t"."UserId"
) AS "t0" ON "u"."Id" = "t0"."Key"

EF Core 10.0.0-rc.2.25502.107 and 10.0.12 (SQLite):

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

EF Core 10.0.12 (SQL Server):

SELECT [u].[Id], COALESCE([t0].[Amount], 0) AS [Amount]
FROM [Users] AS [u]
OUTER APPLY (
    SELECT COALESCE(SUM([t].[Amount]), 0) AS [Amount]
    FROM [Transactions] AS [t]
    GROUP BY [t].[UserId]
    HAVING [t].[UserId] = [u].[Id]
) AS [t0]

What I found

Bisecting the published Microsoft.EntityFrameworkCore.Sqlite packages: preview.1 through rc.1 give the LEFT JOIN; rc.2 is the first that throws.

Among the changes between those tags, #36238 ("Fix DefaultIfEmpty and nullability within SelectMany selector") touches this path. It calls MakeProjectionNullable on the inner select when DefaultIfEmpty is encountered, before the join is added.

My reading, not confirmed with a debugger: for an inner select with GROUP BY, SelectExpression.TryExtractJoinKey only converts the apply to a join if the join-key columns are already projected, checking projectionColumns.Contains(innerColumn). After #36238 the projected key column is a nullable copy, so it no longer equals the non-nullable column taken from the HAVING predicate and the conversion is abandoned.

Projecting the key in the outer select does not change the result. Writing the query as join … into … from … DefaultIfEmpty() still gives the LEFT JOIN, which is a workaround.

Versions

• EF Core: 10.0.12 (also 10.0.0, 10.0.0-rc.2.25502.107). 11.0 previews not tested (no .NET 11 SDK on this machine).
• Providers: Microsoft.EntityFrameworkCore.Sqlite, Microsoft.EntityFrameworkCore.SqlServer; also seen through Microting.EntityFrameworkCore.MySql 10.0.12 (Invalid SQL syntax OUTER APPLY generated for MariaDB microting/Pomelo.EntityFrameworkCore.MySql#467)
• Target framework: net10.0
• OS: Windows 11