EF Core 6：如何优化嵌套字段汇总查询以避免重复代码？

现有嵌套实体结构如下：

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

需求是生成每个Account的汇总信息，因此创建了AccountSummary实体和AccountSummarySpec查询规范：

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 AccountSummary
        {
            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);
    }
}

当前实现可正常运行，但汇总求和部分重复了多次已审批报表的筛选逻辑，随着业务字段增加，重复代码会进一步降低可读性，希望简化这部分逻辑。使用技术栈为EF Core 6 + Ardalis.Specification.EntityFrameworkCore。

1. 用扩展方法封装重复筛选逻辑 #

创建静态扩展方法，把已审批报表的筛选逻辑抽离出来，实现全局复用：

public static class ReportExtensions
{
    // 针对单个Customer返回其已审批报表
    public static IQueryable<Report> ApprovedReports(this Customer customer)
    {
        return customer.Reports.Where(r => r.Status == ReportStatus.Approved);
    }

    // 针对Customer集合返回所有已审批报表
    public static IQueryable<Report> GetAllApprovedReports(this IQueryable<Customer> customers)
    {
        return customers.SelectMany(c => c.Reports.Where(r => r.Status == ReportStatus.Approved));
    }
}

修改后的AccountSummarySpec：

public sealed class AccountSummarySpec : Specification<Account, AccountSummary> {
    public AccountSummarySpec(AccountType type) {
        this.Query.Select(a => new AccountSummary
        {
            Id = a.Id,
            AccountName = a.Name,
            CustomerCount = a.Customers.Count(),
            TotalApprovedValueA = a.Customers.GetAllApprovedReports().Sum(r => r.Measurements.ValueA),
            TotalApprovedValueB = a.Customers.GetAllApprovedReports().Sum(r => r.Measurements.ValueB),
            // 新增字段直接复用GetAllApprovedReports()即可
        }).Where(a => a.Type == type);
    }
}

2. 用中间投影类整合多字段汇总 #

如果需要汇总的字段较多，可以创建中间类封装所有已审批报表的汇总计算，避免重复遍历：

// 中间汇总类，集中处理已审批报表的所有统计逻辑
public class ApprovedReportTotals
{
    public int TotalValueA { get; set; }
    public int TotalValueB { get; set; }
    // 其他新增汇总字段
}

public static class AccountExtensions
{
    public static ApprovedReportTotals CalculateApprovedTotals(this Account account)
    {
        var approvedReports = account.Customers.SelectMany(c => c.Reports.Where(r => r.Status == ReportStatus.Approved));
        return new ApprovedReportTotals
        {
            TotalValueA = approvedReports.Sum(r => r.Measurements.ValueA),
            TotalValueB = approvedReports.Sum(r => r.Measurements.ValueB),
            // 新增字段只需在此添加一次计算逻辑
        };
    }
}

修改后的AccountSummarySpec：

public sealed class AccountSummarySpec : Specification<Account, AccountSummary> {
    public AccountSummarySpec(AccountType type) {
        this.Query.Select(a => new AccountSummary
        {
            Id = a.Id,
            AccountName = a.Name,
            CustomerCount = a.Customers.Count(),
            TotalApprovedValueA = a.CalculateApprovedTotals().TotalValueA,
            TotalApprovedValueB = a.CalculateApprovedTotals().TotalValueB,
            // 新增字段直接取中间类的属性
        }).Where(a => a.Type == type);
    }
}

注意：EF Core 6支持将此类方法转换为SQL，但需确保方法内仅使用EF Core可翻译的LINQ操作。

3. 用Ardalis.Specification片段复用筛选规则 #

如果需要在多个查询规范中复用已审批报表的筛选逻辑，可以创建通用的Specification片段：

public class ApprovedReportsSpec : Specification<Report>
{
    public ApprovedReportsSpec()
    {
        Query.Where(r => r.Status == ReportStatus.Approved);
    }
}

修改后的AccountSummarySpec：

public sealed class AccountSummarySpec : Specification<Account, AccountSummary> {
    public AccountSummarySpec(AccountType type) {
        var approvedReportsSpec = new ApprovedReportsSpec();
        
        Query.Select(a => new AccountSummary
        {
            Id = a.Id,
            AccountName = a.Name,
            CustomerCount = a.Customers.Count(),
            TotalApprovedValueA = a.Customers
                .SelectMany(c => approvedReportsSpec.Evaluate(c.Reports))
                .Sum(r => r.Measurements.ValueA),
            TotalApprovedValueB = a.Customers
                .SelectMany(c => approvedReportsSpec.Evaluate(c.Reports))
                .Sum(r => r.Measurements.ValueB),
        }).Where(a => a.Type == type);
    }
}

这种方式的优势是：后续如果已审批报表的判断规则变化（比如新增其他可用状态），只需修改ApprovedReportsSpec，所有引用的地方会自动更新。

内容的提问来源于stack exchange，提问作者Michael McMullin