EF Core关联银行收支表计算余额时金额重复计算如何解决

涉及实体定义 #

项目中存在3张业务数据表对应实体类，以及1个用于前端展示的视图模型：

• 银行信息表（Bank Info）

public class Bank 
{
    [Key]
    public int ID {get; set;}

    [Required(ErrorMessage ="Bank Name is required")]
    [StringLength(100)]
    public string BankName { get; set; }

    [Required(ErrorMessage = "Bank Name is required")]
    [StringLength(100)]
    public string AccountNo { get; set; }
}

• 支出明细表（Expense Details）

public class Expense 
{
    [Key]
    public int ID {get; set;}

    public int SubAccountID { get; set; }
    [ForeignKey(nameof(SubAccountID))]
    public virtual SubAccount SubAccount { get; set; }

    public int MonthID { get; set; }
    [ForeignKey(nameof(MonthID))]
    public virtual Month Month { get; set; }

    public int Year { get; set; }
    public DateTime ReceiptDate { get; set; }
    [Display(Name = "Beneficiary")]
    [StringLength(100)]
    [Required(ErrorMessage = "Beneficiary is required")]
    public string Beneficiary { get; set; }
    [Required(ErrorMessage = "Ammount is required")]
    public double ReceiptAmount { get; set; }

    [Display(Name = "Description")]
    [StringLength(300)]
    public string Description { get; set; }

    [Required(ErrorMessage ="Bank is required")]
    [Display(Name ="Bank")]
    public int BanksID { get; set; }
    [ForeignKey(nameof(BanksID))]
    public virtual Bank Bank { get; set; }
}

• 收入明细表（Income Details）

public class IncomeTransaction         
{
    [Key]
    public int ID { get; set; }

    [Required(ErrorMessage ="Bank Info is required")]

    public int BankID { get; set; }
    [ForeignKey(nameof(BankID))]
    public Bank Bank { get; set; }

    [Display(Name ="Transaction No")]
    [StringLength(100)]
    public string TransactionNo { get; set; }

    [Required(ErrorMessage = "Amount is required")]
    public double Amount { get; set; }

    [Required(ErrorMessage = "Transaction Date is required")]
    [Display(Name = "Transaction Date")]
    public DateTime TransactionDate { get; set; }

    [Required(ErrorMessage ="Income Source is required")]
    [StringLength(100)] 
    [Display(Name ="Income Source")]
    public string IncomeSource { get; set; }
}

• 银行余额展示视图模型（Shared BankModel）

public class SharedBank 
{
    public int ID {get; set;}
    [Required(ErrorMessage = "Bank Name is required")]
    [StringLength(100)]
    public string BankName { get; set; }

    [Required(ErrorMessage = "Bank Name is required")]
    [StringLength(100)]
    public string AccountNo { get; set; }

    [NotMapped]
    public double Balance { get; set; }
}

问题描述 #

需求为返回每个银行的基础信息+对应账户当前余额，原有查询逻辑运行后出现金额计算错误：收入金额会被支出记录条数乘算，例如某银行存在1条金额2000的收入记录、3条支出记录时，收入额会被错误计算为6000，无法得到正确的收支差值。
原有错误实现代码如下：

public async Task<IEnumerable<SharedBank>> GetBankBalances()
{
    return await (
        from bank in _pisa.Banks
        join trans in _pisa.IncomeTransactions on bank.ID equals trans.BankID
        join exp in _pisa.Expenses on bank.ID equals exp.BanksID
        group new {trans,exp, bank} by new {trans.BankID, exp.BanksID, bank.ID,bank.BankName,bank.AccountNo} into g
        select new SharedBank()
        {
            ID = g.Key.ID,
            AccountNo = g.Key.AccountNo,
            BankName = g.Key.BankName,
            Balance = g.Sum(o=> o.trans.Amount - o.exp.ReceiptAmount )

        })
    .Distinct()
    .ToListAsync();          
}

错误根因：直接对收入表、支出表做连续内连接会产生笛卡尔积，1条收入匹配N条支出时，该条收入会在结果集中重复出现N次，求和时自然会被累计N次，最终结果翻倍。

正确实现方案 #

不要直接关联两张流水明细表做连接，先分别按银行维度聚合计算总收入、总支出，再和银行基础信息关联计算余额，从根源避免笛卡尔积导致的重复计算，同时兼容无流水记录的银行账户场景：

public async Task<IEnumerable<SharedBank>> GetBankBalances()
{
    // 按银行ID聚合所有收入总和
    var incomeAgg = _pisa.IncomeTransactions
        .GroupBy(t => t.BankID)
        .Select(g => new 
        {
            BankID = g.Key,
            TotalIncome = g.Sum(t => t.Amount)
        });
    
    // 按银行ID聚合所有支出总和
    var expenseAgg = _pisa.Expenses
        .GroupBy(e => e.BanksID)
        .Select(g => new 
        {
            BankID = g.Key,
            TotalExpense = g.Sum(e => e.ReceiptAmount)
        });

    // 左连接聚合结果，计算余额，无流水时收支默认按0计算
    return await (
        from bank in _pisa.Banks
        join inc in incomeAgg on bank.ID equals inc.BankID into incTemp
        from inc in incTemp.DefaultIfEmpty()
        join exp in expenseAgg on bank.ID equals exp.BankID into expTemp
        from exp in expTemp.DefaultIfEmpty()
        select new SharedBank()
        {
            ID = bank.ID,
            AccountNo = bank.AccountNo,
            BankName = bank.BankName,
            Balance = (inc?.TotalIncome ?? 0) - (exp?.TotalExpense ?? 0)
        }
    ).ToListAsync();
}

实现说明：

• 先聚合再关联的方式保证关联时银行和收入总和、支出总和都是1对1匹配，不会产生重复行
• DefaultIfEmpty()实现左连接效果，不会过滤掉还没有收入/支出记录的银行账户
• 空值合并运算符?? 0处理无流水场景的空值，避免空引用报错，同时保证新开账户余额默认显示为0

参考截图 #

• 银行信息表
• 收入明细表
• 支出明细表
• 余额展示页面

内容的提问来源于stack exchange，提问作者Shu'aib Ahmed Du'ale