Linq Sum基于一些专栏(Linq Sum based on some columns)

Linq Sum基于一些专栏(Linq Sum based on some columns)

我有一个查询返回特定CompanyId和特定月/年的一些值。

我想报一年的报告。 （所以我需要总结不同月份的价值，但同年和CompanyId）

这是我的查询：

from er in EconomicReports join com in Companies on er.CompanyId equals com.Id join cou in Countries on com.CountryId equals cou.Id where er.Year == 2014 select new { Country = cou.Name, CompanyId = com.Id, CorporationId = com.CorporationId, Year = er.Year, RegisteredCases = er.NewCasesTotalCount, RegisteredCasesAmount = er.NewCasesTotalAmount, ResolvedCases = er.ClosedCasesTotalCount, ResolvedCasesAmount = er.ClosedCasesCapitalAmount + er.ClosedCasesInterestAmount, ActiveCases = (er.NewCasesTotalCount ?? 0) - (er.ClosedCasesTotalCount ?? 0), ActiveCasesAmount = (er.NewCasesTotalAmount ?? 0) - (er.ClosedCasesCapitalAmount ?? 0) - (er.ClosedCasesInterestAmount ?? 0) }

基本上，第1行和第5行需要是一行，因为它们来自同一年，同一公司Id（我在结果中也将月份也看作是另一个月，但是同一年）

I have a query returning some values for a specific CompanyId and a Specific Month/Year.

I want to make a report for the whole year. (So I need to sum up the values from different months, but of the same year and CompanyId)

Here is my query now:

from er in EconomicReports join com in Companies on er.CompanyId equals com.Id join cou in Countries on com.CountryId equals cou.Id where er.Year == 2014 select new { Country = cou.Name, CompanyId = com.Id, CorporationId = com.CorporationId, Year = er.Year, RegisteredCases = er.NewCasesTotalCount, RegisteredCasesAmount = er.NewCasesTotalAmount, ResolvedCases = er.ClosedCasesTotalCount, ResolvedCasesAmount = er.ClosedCasesCapitalAmount + er.ClosedCasesInterestAmount, ActiveCases = (er.NewCasesTotalCount ?? 0) - (er.ClosedCasesTotalCount ?? 0), ActiveCasesAmount = (er.NewCasesTotalAmount ?? 0) - (er.ClosedCasesCapitalAmount ?? 0) - (er.ClosedCasesInterestAmount ?? 0) }

Basically, rows 1 and 5 need to be one row, because they are from the same year, same company Id (I have put also Month in the results for you to see that is a different month, but same year)

最满意答案

你需要一个GroupBy ：

var query = (from er in EconomicReports join com in Companies on er.CompanyId equals com.Id join cou in Countries on com.CountryId equals cou.Id where er.Year == 2014 select new { Country = cou.Name, CompanyId = com.Id, CorporationId = com.CorporationId, Year = er.Year, RegisteredCases = er.NewCasesTotalCount, RegisteredCasesAmount = er.NewCasesTotalAmount, ResolvedCases = er.ClosedCasesTotalCount, ResolvedCasesAmount = er.ClosedCasesCapitalAmount + er.ClosedCasesInterestAmount, ActiveCases = (er.NewCasesTotalCount ?? 0) - (er.ClosedCasesTotalCount ?? 0), ActiveCasesAmount = (er.NewCasesTotalAmount ?? 0) - (er.ClosedCasesCapitalAmount ?? 0) - (er.ClosedCasesInterestAmount ?? 0) }); var result = query .GroupBy(u => new {u.Country, u.CompanyId, u.CorporationId, u.Year}) .Select(u => new { Country = u.Key.Country, CompanyId = u.Key.CompanyId, CorporationId = u.Key.CorporationId, Year = u.Key.Year, RegisteredCases = u.Select(t => t.RegisteredCases).DefaultIfEmpty().Sum(), RegisteredCasesAmount = u.Select(t => t.RegisteredCasesAmount).DefaultIfEmpty().Sum(), ResolvedCases = u.Select(t => t.ResolvedCases).DefaultIfEmpty().Sum(), ResolvedCasesAmount = u.Select(t => t.ResolvedCasesAmount).DefaultIfEmpty().Sum(), ActiveCases = u.Select(t => t.ActiveCases).DefaultIfEmpty().Sum(), ActiveCasesAmount = u.Select(t => t.ActiveCasesAmount).DefaultIfEmpty().Sum() }) .ToList();

我认为对于基于CompanyId和Year分组记录， Country和CorporationId必须相同，所以我将它们包含在group by中，用于Select

you need a GroupBy:

var query = (from er in EconomicReports join com in Companies on er.CompanyId equals com.Id join cou in Countries on com.CountryId equals cou.Id where er.Year == 2014 select new { Country = cou.Name, CompanyId = com.Id, CorporationId = com.CorporationId, Year = er.Year, RegisteredCases = er.NewCasesTotalCount, RegisteredCasesAmount = er.NewCasesTotalAmount, ResolvedCases = er.ClosedCasesTotalCount, ResolvedCasesAmount = er.ClosedCasesCapitalAmount + er.ClosedCasesInterestAmount, ActiveCases = (er.NewCasesTotalCount ?? 0) - (er.ClosedCasesTotalCount ?? 0), ActiveCasesAmount = (er.NewCasesTotalAmount ?? 0) - (er.ClosedCasesCapitalAmount ?? 0) - (er.ClosedCasesInterestAmount ?? 0) }); var result = query .GroupBy(u => new {u.Country, u.CompanyId, u.CorporationId, u.Year}) .Select(u => new { Country = u.Key.Country, CompanyId = u.Key.CompanyId, CorporationId = u.Key.CorporationId, Year = u.Key.Year, RegisteredCases = u.Select(t => t.RegisteredCases).DefaultIfEmpty().Sum(), RegisteredCasesAmount = u.Select(t => t.RegisteredCasesAmount).DefaultIfEmpty().Sum(), ResolvedCases = u.Select(t => t.ResolvedCases).DefaultIfEmpty().Sum(), ResolvedCasesAmount = u.Select(t => t.ResolvedCasesAmount).DefaultIfEmpty().Sum(), ActiveCases = u.Select(t => t.ActiveCases).DefaultIfEmpty().Sum(), ActiveCasesAmount = u.Select(t => t.ActiveCasesAmount).DefaultIfEmpty().Sum() }) .ToList();

i think Country and CorporationId must be same for grouped records based on CompanyId and Year, so i was included them in group by, to use in Select