EF Core 3.1 unexpected SQL generation when combine join and group by

Question

I have this piece of C# code (using EF Core 3.1) that performs a SQL left join operation.

var groupedQuery = query.GroupBy(x => x.CreatedTime.Date)
    .Select(o => new
    {
        Date = o.Key,
        Count = o.Count()
    });

var acceptedGroupedQuery = query.Where(o => o.Accepted).GroupBy(x => x.CreatedTime.Date)
    .Select(o => new
    {
        Date = o.Key,
        Count = o.Count()
    });

var projectedQuery = groupedQuery.GroupJoin(acceptedGroupedQuery, o => o.Date, i => i.Date,
        (o, i) => new { Total = o, Accepted = i })
    .SelectMany(o => o.Accepted.DefaultIfEmpty(), (joined, accepted) => new 
    {
        Date = joined.Total.Date,
        Total = joined.Total.Count,
        Accepted = accepted.Count
    });

var result = await projectedQuery
    .Skip(model.Skip).Take(model.Take).ToArrayAsync(cancellationToken: cancellationToken);

The corresponding SQL generation is

exec sp_executesql N'SELECT [t].[c] AS [Date], CAST([t].[c0] AS float) AS [Total], CAST([t0].[c0] AS float) AS [Accepted]
FROM (
    SELECT CONVERT(date, [p].[CreatedTime]) AS [c], COUNT(*) AS [c0]
    FROM [PartnerInvitation] AS [p]
    WHERE [p].[DeletedTime] IS NULL
    GROUP BY CONVERT(date, [p].[CreatedTime])
) AS [t]
LEFT JOIN (
    SELECT CONVERT(date, [p0].[CreatedTime]) AS [c], [t].[c0]
    FROM [PartnerInvitation] AS [p0]
    WHERE [p0].[Accepted] = 1 
    GROUP BY CONVERT(date, [p0].[CreatedTime])
) AS [t0] ON [t].[c] = [t0].[c]
ORDER BY (SELECT 1)
OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY',N'@__p_0 int,@__p_1 int',@__p_0=0,@__p_1=10

The above query gives a following error:

Msg 4104, Level 16, State 1, Line 9. The multi-part identifier "t.c0" could not be bound.

I expect the second select statement in left join would be SELECT ..., COUNT(*) as [c0] instead of SELECT ..., t.[c0]

Any idea what happened here and how to solve it?

user_5202563 · Answer

Any idea what happened here

Apparently EF Core 3.x bug - in EF Core 5.x the same query gets the expected correct translation.

how to solve it?

It's basically impossible to fix library bugs. The good thing is that it is fixed, the bad is that it is fixed in v5.x and not in v3.1 - it appears in the latest at this time v3.1.14, and since they are already working on v6.0, I don't think it would ever be fixed in v3.1.

So one option is to upgrade to the latest v5.x.

Another option, but just for this specific query is to rewrite it in way which doesn't use join - what it does could easily and more efficiently be achieved with single GroupBy query and conditional Sum (in v5.x you could also use conditional Count), e.g. get rid of groupedQuery and acceptedGroupedQuery and just do

var projectedQuery = query
    .GroupBy(e => e.CreatedTime.Date)
    .Select(g => new
    {
        Date = g.Key,
        Total = g.Count(),
        Accepted = g.Sum(e => e.Accepted ? 1 : 0)
    });

EF Core 3.1 unexpected SQL generation when combine join and group by

1 Answers