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.
CodePudding user response:
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();