CODEWITHSUNDEEP
entity-frameworklinqentity-framework-core
Legion
Legion

Reputation: 3427

How do you use aggregate functions in LINQ?

I can do this really easily in SQL but I can't translate it into LINQ.

The 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

This is my broken attempt at translation:

            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 complains first that dd is not declared and then really complains once it reaches the join to Fxrates.

Upvotes: 0

Views: 57

Answers (1)

Guilherme Meinlschmiedt Abdo
Guilherme Meinlschmiedt Abdo

Reputation: 731

I think this may be close to what you want:

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

Upvotes: 2

Related Questions