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.

score 1 · Answer 1 · answered Dec 05 '22 at 14:43

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.

answered Dec 05 '22 at 14:43

Svyatoslav Danyliv

21,911
3
16
32

Thanks for the reply, that's very helpful. In my haste to provide a simple version, I neglected to mention that there are other fields required for the summary outside of Measurements -- I've updated my original post. Also note that `this.Query` refers to the Ardalis.Specification library rather than an `IQueryable`, so assignment isn't required. However, I'm not sure if fluent syntax is supported in this context (I could be mistaken). – Michael McMullin Dec 06 '22 at 11:09
That's terrible that some library introduced another `IQueryable` analogue. I can update answer, but without `IQueryable` it can be wasting time. – Svyatoslav Danyliv Dec 06 '22 at 11:15
I would suggest to do not use specifications for statistical queries, they needs more tuning and simple `Select` is not enough. Usually I use extension methods for such functionality. – Svyatoslav Danyliv Dec 06 '22 at 11:31

EF Core summary of nested fields

1 Answers1