Select with JOIN, Group BY and SUM in related 1:n tables (2nd level of child table) with LINQ / EF-C-CodePudding

I have these tables which have 1:n and then 1:n relationship with each other:

How can I sum up the amount of Expenses for one specific household?

This is my SQL for that:

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;

This is what I have done so far:

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();

CodePudding user response：

LNQ grouping has the same behaviour as in the SQL. If you add additional grouping keys - you will fail. Important part is what to group. Also I have removed other artifacts which are not present in your original SQL. You have test that adding additional grouping keys will not change result.

var householdId  = ...;

var query = 
    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
    where household.Id == householdId
    group expense
        by new { household.Id, household.Name }
            into g
    select new 
    { 
        HouseholdId = g.Key.Id, 
        HouseholdName = g.Key.Name, 
        SumExpenses = g.Sum(x => x.Amount)
    };