EF Core 6, GroupBy with inner collection Sum

Question

I want to create a query which will group a set by some criteria, and it will create a result set witch will contain the sum of some inner list.

This is my query, which fails:

var invoices = await _dbContext.Beneficiaries
                 .Where(dbEntry => dbEntry.Id == beneficiaryId && dbEntry.ProviderId == providerId)
                 .SelectMany(dbEntry => dbEntry.Invoices)
                 .GroupBy(dbEntry => dbEntry.IssueDate.Month)
                 .Select(dbEntry => new
                 {
                     IssueMonth = dbEntry.Key,
                     VAT = dbEntry.Max(invoice => invoice.VAT),
                     TotalPay = dbEntry.Select(invoice => invoice.InvoiceEntries.Sum(entry => entry.DelegateHourlyRate)).Max(),
                     TotalSell = dbEntry.Select(invoice => invoice.InvoiceEntries.Sum(entry => entry.BeneficiaryHourlyRate)).Max(),
                 })
            .Where(group => group.IssueMonth <= _todayDate.UtcNow.Month && group.IssueMonth >= _todayDate.UtcNow.Month - (int)by)
            .ToListAsync();

Following is the class hierarchy

public class Beneficiary
{
    public ICollection<Invoice> Invoices { get; set; }
}

public class Invoice
{
    public ICollection<InvoiceEntry> InvoiceEntries { get; set; }
}

public class InvoiceEntry
{
    public decimal DelegateHourlyRate { get; set; }
    public decimal BeneficiaryHourlyRate { get; set; }
}

This is the exception I'm getting with EF version 5.0.9.

The LINQ expression 'GroupByShaperExpression: KeySelector: b.IssueDate, ElementSelector:EntityShaperExpression: EntityType: Invoice ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember IsNullable: True .Max(invoice => invoice.InvoiceEntries.Count)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

This is the exception I'm getting with EF version 6.0.8.

SqlException: Cannot perform an aggregate function on an expression containing an aggregate or a subquery. Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

<ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="6.0.8" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="6.0.8">
        <PrivateAssets>all</PrivateAssets>
        <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="6.0.8" />
</ItemGroup>

Another variation

var invoices2 = await _dbContext.Beneficiaries
     .Where(dbEntry => dbEntry.Id == beneficiaryId && dbEntry.ProviderId == providerId)
     .SelectMany(dbEntry => dbEntry.Invoices
         .GroupBy(dbEntry => dbEntry.IssueDate.Month)
         .Select(dbEntry => new
         {
             IssueMonth = dbEntry.Key,
             VAT = dbEntry.Sum(invoice => invoice.VAT),
             TotalPay = dbEntry.Sum(invoice => invoice.InvoiceEntries.Sum(entry => entry.DelegateHourlyRate)),
             TotalSell = dbEntry.Sum(invoice => invoice.InvoiceEntries.Sum(entry => entry.BeneficiaryHourlyRate))
         }))
 .Where(group => group.IssueMonth <= _todayDate.UtcNow.Month && group.IssueMonth >= _todayDate.UtcNow.Month - (int)by)
 .ToListAsync();

Which results in

SqlException: Cannot perform an aggregate function on an expression containing an aggregate or a subquery. Cannot perform an aggregate function on an expression containing an aggregate or a subquery. Cannot perform an aggregate function on an expression containing an aggregate or a subquery. Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Answer 1

If I understand your query correctly, it should be another SelectMany. Rewritten query in Query Syntax for readability and change time range condition for using table indexes, if they are exist for sure.

var current = _todayDate.UtcNow;
var prev = current.Date.AddMonths(-1);

var query = 
    from b in _dbContext.Beneficiaries
    from invoice in b.Invoices
    where invoice.IssueDate <= current && invoice.IssueDate >= prev
    from invoiceEntry in invoice.InvoiceEntries
    group new { invoice, invoiceEntry } by new { invoice.IssueDate.Year, invoice.IssueDate.Month } into g
    select new 
    {
        IssueMonth = g.Key.Month,
        VAT = g.Max(x => x.invoice.VAT),
        TotalPay = g.Sum(x => x.invoiceEntry.DelegateHourlyRate),
        TotalSell = g.Sum(x => x.invoiceEntry.BeneficiaryHourlyRate)
    };

var invoices = query.ToList();