entity-framework - 如何在 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.

标签： entity-frameworklinqentity-framework-core

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