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

Question

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

Answer 1

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()
    });