如何在LINQ中使用聚合函数?

Question

我可以很容易地在SQL中做到这一点，但我不能将其转换为LINQ。

SQL:

SELECT EmployeeId
,YEAR(DistributionDate) AS DistributionYear
,SUM(SalesLocal * FX.ExchangeRate) AS TotalDistributions
FROM Distributions AS DD INNER JOIN FiscalPeriod AS FP ON DD.DistributionDate BETWEEN FP.StartDate AND FP.EndDate
INNER JOIN FXRates AS FX ON FP.FiscalPeriodId = FX.FiscalPeriodId AND FX.FromCurrencyId = DD.CurrencyId AND FX.ToCurrencyId = 'USD'
GROUP BY EmployeeId, YEAR(DistributionDate)
ORDER BY EmployeeId, DistributionYear

这是我失败的翻译尝试:

var pointList = (from dd in db.Distributions
join e in db.Employee on dd.EmployeeId equals e.EmployeeId
join fp in db.FiscalPeriod.Where(p => dd.DistributionDate >= p.StartDate && dd.DistributionDate <= p.EndDate)
join fx in db.Fxrates on (fp.FiscalPeriodId equals fx.FiscalPeriodId) && (fx.FromCurrencyId equals dd.CurrencyId) && (fx.ToCurrencyId equals "USD")
group by dd.EmployeeId && dd.DistributionDate.Year
select new Point<int, decimal?>
{
X = dd.DistributionDate.Year,
Y = dd.Sum(dd.SalesLocal * fx.ExchangeRate)
}).ToList();

Visual Studio首先抱怨dd没有声明，然后在到达Fxrates的连接处时才真正抱怨。

Answer 1

我认为这可能接近你想要的:

var pointList = (from dd in db.Distributions
join e in db.Employee on dd.EmployeeId equals e.EmployeeId
from fp in db.FiscalPeriod.Where(p => dd.DistributionDate >= p.StartDate && dd.DistributionDate <= p.EndDate)
from fx in db.Fxrates.Where(fx2 => fp.FiscalPeriodId == fx2.FiscalPeriodId && fx2.FromCurrencyId == dd.CurrencyId && fx2.ToCurrencyId == "USD")
group new { dd, fx } by new { dd.EmployeeId, dd.DistributionDate.Year } into distGroup
select new 
{
X = distGroup.Key.Year,
Y = distGroup.Sum(d => d.dd.SalesLocal * d.fx.ExchangeRate)
}).ToList();