我有這些表，它們之間有 1:n 和 1: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;
這是我到目前為止所做的：
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();
uj5u.com熱心網友回復：
LNQ 分組具有與 SQL 中相同的行為。如果您添加其他分組鍵 - 您將失敗。重要的部分是分組內容。此外，我還洗掉了原始 SQL 中不存在的其他工件。您已經測驗添加額外的分組鍵不會改變結果。
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)
};
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/530628.html
標籤：C＃sql实体框架林克