如何用LINQ/EF Core计算特定家庭的二级关联支出总和

背景说明 #

• 三张表关联关系：Households（家庭）→Accounts（账户）→Expenses（支出）为1:n:n（一个家庭对应多个账户，一个账户对应多笔支出）
• 需求：计算指定家庭的支出金额总和，对应的SQL实现如下：

SELECT households.Id as HouseholdId,
       households.Name HouseholdName,
       SUM(expenses.Amount) as SumExpenses
FROM [Households] households
INNER JOIN Accounts accounts
    ON households.Id = accounts.HouseholdId
INNER JOIn Expenses expenses
    ON expenses.AccountId = accounts.Id
WHERE households.Id = '2AFAB095-39D6-4637-1FC1-08DAC249FA0A' 
GROUP BY households.Id, households.Name;

现有LINQ问题 #

已编写部分LINQ代码，但求和逻辑未完成，且分组逻辑存在错误：

var results = await (
    from household in Context.Households
    join account in Context.Accounts
        on household.Id equals account.HouseholdId
    join expense in Context.Expenses
        on account.Id equals expense.AccountId
    group new { household }
        by new { household.Id, household.Name, AccountName = account.Name, Amount = expense.Amount}
            into g
    select new 
    { 
        HouseholdId = g.Key.Id, 
        HouseholdName = g.Key.Name, 
        AccountName = g.Key.AccountName,
        //What to do here to get the sum?
    }).ToListAsync();

正确实现方式 #

情况1：按家庭维度统计总支出（与SQL逻辑完全对齐） #

修改分组逻辑，仅按家庭ID和名称分组，再对分组内的支出金额求和：

var targetHouseholdId = Guid.Parse("2AFAB095-39D6-4637-1FC1-08DAC249FA0A");
var results = await (
    from household in Context.Households
    where household.Id == targetHouseholdId
    join account in Context.Accounts
        on household.Id equals account.HouseholdId
    join expense in Context.Expenses
        on account.Id equals expense.AccountId
    // 仅按家庭维度分组，避免不必要的分组字段
    group expense by new { household.Id, household.Name } into g
    select new 
    { 
        HouseholdId = g.Key.Id, 
        HouseholdName = g.Key.Name, 
        SumExpenses = g.Sum(e => e.Amount) // 对分组内所有支出金额求和
    }).ToListAsync();

情况2：按家庭+账户维度统计各账户支出总和 #

如果需要统计每个账户的支出明细，调整分组维度为家庭+账户，再求和：

var targetHouseholdId = Guid.Parse("2AFAB095-39D6-4637-1FC1-08DAC249FA0A");
var results = await (
    from household in Context.Households
    where household.Id == targetHouseholdId
    join account in Context.Accounts
        on household.Id equals account.HouseholdId
    join expense in Context.Expenses
        on account.Id equals expense.AccountId
    // 按家庭+账户维度分组
    group expense by new { household.Id, household.Name, AccountName = account.Name } into g
    select new 
    { 
        HouseholdId = g.Key.Id, 
        HouseholdName = g.Key.Name, 
        AccountName = g.Key.AccountName,
        SumExpenses = g.Sum(e => e.Amount) // 对分组内该账户的支出金额求和
    }).ToListAsync();

关键修改点 #

• 提前添加where过滤条件，减少关联的数据量，与SQL逻辑保持一致
• 分组时不要将Amount放入分组Key，否则会按每笔支出的金额拆分分组，无法得到总和
• 使用g.Sum(e => e.Amount)实现SQL中SUM(expenses.Amount)的求和逻辑

内容的提问来源于stack exchange，提问作者Benjamin Martin