How can I make EF Core 3 translate the group by foreign key the same way efcore7 does it?

I have the following entities and a database context

public class Item
{
    public int Id { get; set; }

    public int? ReceiptId { get; set; }
    public int ItemTypeId { get; set; }
}

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

    public List<Item> Items { get; set; }
}

public class Receipt
{
    public int Id { get; set; }
    public string ReceiptInfo { get; set; }

    public List<Item> Items { get; set; }
}

I'm trying to get a the list of receipts, but instead of containing the items they contain, I want them to have the itemType and the amount of items for each. I have written the following linq query, which works:

var result = _databaseContext.Receipts.Select(r => new
            {
                r.Id,
                r.ReceiptInfo,
                ItemInfo = r.Items.GroupBy(item => item.ItemTypeId)
                                  .Select(group => new
                                  {
                                      IdItemType = group.Key,
                                      AmountOfItems = group.Count(),
                                  }).ToList()

            });

With EF Core 7, it is translated to the following SQL query:

SELECT [r].[Id], [r].[ReceiptInfo], [t].[IdItemType], [t].[AmountOfItems]
FROM [Receipts] AS [r]
OUTER APPLY 
    (SELECT [i].[ItemTypeId] AS [IdItemType], COUNT(*) AS [AmountOfItems]
     FROM [Items] AS [i]
     WHERE [r].[Id] = [i].[ReceiptId]
     GROUP BY [i].[ItemTypeId]) AS [t]
ORDER BY [r].[Id]

Yet, I need to do this in an older project which doesn't support a version older than 3.1 for EF Core.

There it translates the query differently and I get this error

> Column 'Receipts.Id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

In case of EF Core 3.1, you have to postprocess loaded detail items on the client side:

var rawData = _databaseContext.Receipts.Select(r => new
    {
        r.Id,
        r.ReceiptInfo,
        RawItemInfo = r.Items.Select(item => new
        {
            IdItemType = item.ItemTypeId
        }).ToList()
    })
    .ToList();

var result = rawData
    .Select(r => new
    {
        r.Id,
        r.ReceiptInfo,
        ItemInfo = r.RawItemInfo
            .GroupBy(item => item.ItemTypeId)
            .Select(group => new
            {
                IdItemType = group.Key,
                AmountOfItems = group.Count(),
            }).ToList()
    });

As you see, GroupBy support has improved drastically in EFC 7. EFC 3 only supports GroupBy with aggregates at the query root.

Therefore, to make it run in EFC 3 you need to force the query into the supported shape. To get the same grouping level, the query starts at Items and groups + aggregates once over three elements instead of two:

var result = _databaseContext.Items
    .GroupBy(item => new { item.ReceiptId, item.Receipt.ReceiptInfo, item.ItemTypeId })
    .Select(group => new
    {
        Id = group.Key.ReceiptId,
        ReceiptInfo = group.Key.ReceiptInfo,
        IdItemType = group.Key.ItemTypeId,
        NrOfItems = group.Count(),
    })

That returns the same data as the original query and does the reduction of data (aggregate) in the database. To get the same result shape, it needs some post-processing in-memory (i.e. after calling AsEnumerable()):

    .AsEnumerable()
    .GroupBy(x => new { x.Id, x.Receipt.ReceiptInfo })
    .Select(g => new
    {
        g.Key.Id,
        g.Key.ReceiptInfo,
        ItemInfo = g.Select(x => new { x.IdItemType, x.NrOfItems })
       
    });

This requires adding a navigation property Item.Receipt.