EF Core summary of nested fields

Question

I have a nested object similar to the diagram below:

Account
├─ Id
├─ Type
├─ Name
└─ Customers[]
   ├─ Id
   ├─ Name
   └─ Reports[]
      ├─ Id
      ├─ Status
      └─ Measurements[]
         ├─ Id
         ├─ ValueA
         └─ ValueB

One of the requirements is to create a summary of each account, so I created a separate entity and a specification to get the data I need:

public class AccountSummary : EntityBase<int> {
    public string AccountName { get; set; }
    public int CustomerCount {get; set; }
    public int TotalApprovedValueA { get;set; }
    public int TotalApprovedValueB { get;set; }
}

public sealed class AccountSummarySpec : Specification<Account, AccountSummary> {
    public AccountSummarySpec(AccountType type) {
        this.Query.Select(a => new AccountTable
        {
            Id = a.Id,
            AccountName = a.Name,
            CustomerCount = a.Customers.Count(),
            TotalApprovedValueA = a.Customers
                    .SelectMany(s => s.Reports.Where(r => r.Status == ReportStatus.Approved))
                    .Sum(r => r.Measurements.ValueA),
            TotalApprovedValueB = a.Customers
                    .SelectMany(s => s.Reports.Where(r => r.Status == ReportStatus.Approved))
                    .Sum(r => r.Measurements.ValueB),
        }).Where(a => a.Type == type)
    }
}

This seems to work ok, but I'm wondering is there a cleaner way of expressing the total sums, as the code is quite repetitive. Something like:

a.Customers.SelectMany(s => s.Reports.Where(r => r.Status == ReportStatus.Approved)) {
    r => {
        TotalApprovedValueA = r.Sum(r.Measurements.ValueA),
        TotalApprovedValueB = r.Sum(r.Measurements.ValueB),
    }
}

The real object I'm working with is far more complex, so repeating the same initial query to select all the approved reports each time is making it quite difficult to follow. Is there a best-practice for avoiding this kind of repetition?

I'm using version 6 of EF-Core and Ardalis.Specification.EntityFrameworkCore.

Edit: Added additional fields to summary class and query.

Answer 1

You can do that more effective:

var query = 
    from a in this.Query
    from c in a.Customers
    from r in c.Reports
    from m in r.Measurements
    where r.Status == ReportStatus.Approved && a.Type == type
    group m by a.Id into g
    select new AccountTable
    {
        Id = g.Key,
        TotalApprovedValueA = g.Sum(x => x.ValueA),
        TotalApprovedValueB = g.Sum(x => x.ValueB),
    };

Note that probably your sample code has a bug, because I do not see query assignment. IQueryable is immutable.