Group by with DefaultIfEmpty on a sub-query with a left join changes the subquery's joins to inner joins

[neoGeneva]

File a bug

The projection starts of being correct, but after joining to it using DefaultIfEmpty() the resulting query ends up incorrect. This only seems to happen when using from x in db.xx.Where().DefaultIfEmpty() syntax to join, join x in db.xx version seems okay (see below).

So, this sample code using Microsoft.EntityFrameworkCore.Sqlite but this also happens in Microsoft.EntityFrameworkCore.SqlServer. I've attached the full project here: Console3.zip

using Console3;
using Microsoft.EntityFrameworkCore;

using (var db = new BloggingContext())
{
    var postCount = db.Posts
        .GroupBy(x => x.BlogId, (x, g) => new { BlogId = x, Count = (int?)g.Count() });

    var totalCounts =
        from b in db.Blogs
        from pc1 in postCount.Where(x => x.BlogId == b.BlogId).DefaultIfEmpty()
        group new { b.BlogId, pc1.Count } by b.BlogId into g
        select new
        {
            BlogId = g.Key,
            Count = g.Sum(x => x.Count)
        };

    var query =
        from b in db.Blogs
        from c in totalCounts.Where(x => x.BlogId == b.BlogId).DefaultIfEmpty()
        select new
        {
            b.BlogId,
            b.Url,
            c.Count
        };

    Console.WriteLine(totalCounts.ToQueryString());
    Console.WriteLine(query.ToQueryString());
}

Produces the following output:

SELECT "b"."BlogId", COALESCE(SUM("t"."Count"), 0) AS "Count"
FROM "Blogs" AS "b"
LEFT JOIN (
    SELECT "p"."BlogId", COUNT(*) AS "Count"
    FROM "Posts" AS "p"
    GROUP BY "p"."BlogId"
) AS "t" ON "b"."BlogId" = "t"."BlogId"
GROUP BY "b"."BlogId"

SELECT "b"."BlogId", "b"."Url", "t0"."Count"
FROM "Blogs" AS "b"
LEFT JOIN (
    SELECT "b0"."BlogId", COALESCE(SUM("t"."Count"), 0) AS "Count"
    FROM "Blogs" AS "b0"
    INNER JOIN (
        SELECT "p"."BlogId", COUNT(*) AS "Count"
        FROM "Posts" AS "p"
        GROUP BY "p"."BlogId"
    ) AS "t" ON "b0"."BlogId" = "t"."BlogId"
    GROUP BY "b0"."BlogId"
) AS "t0" ON "b"."BlogId" = "t0"."BlogId"

As you can see the inner most join is changed for a left join to an inner join.

I've found that using more traditional join syntax produces the correct SQL. So this works:

    var totalCounts =
        from b in db.Blogs
        // from pc1 in postCount.Where(x => x.BlogId == b.BlogId).DefaultIfEmpty()
        join x in postCount on b.BlogId equals x.BlogId into x
        from pc1 in x.DefaultIfEmpty()
        group new { b.BlogId, pc1.Count } by b.BlogId into g
        select new
        {
            BlogId = g.Key,
            Count = g.Sum(x => x.Count)
        };

Include provider and version information

EF Core version: 6.0.2 (and I've confirmed it exists in 6.0.0 and 6.0.1)
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 6.0
Operating system: Windows 11
IDE: VSCode 1.64.2

[AndriySvyryd]

Could be related to #27297

[smitpatel]

CorrelationFindingExpressionVisitor removes DefaultIfEmpty but it is supposed to remove the one in the last chain. It ends removing nested inner ones too. Added regression test skipped with issue number. Also add regression test with non-group by scenario.

[smitpatel]

Related #20291