如何在LINQ中使用GroupBy和Sum

Question

我正在使用SelectMany在以下查询中与GroupBy和Sum作斗争。谁能告诉我如何对两个字段求和，以及如何对多个字段进行分组和排序?

var Rows = allData.SelectMany(u => u._rows.Select(t => new
{
  OA = t[4],
  CD = t[5],
  PD = t[0],
  DS = Convert.ToInt32(t[9]),
  CS = Convert.ToInt32(t[10])
}))
// Pseudo-code:
//.GroupBy(CD)
//.GroupBy(OA)
//.GroupBy(PD)
//.Sum(u=> u.DS)
//.Sum(u => u.CS)
.OrderBy(u => u.CD)
.ThenBy(u => u.OA)
.ThenBy(u => u.PD)
.ToList();

对象:

List<DataProperty> allData = new List<DataProperty>();         

DataProperty包含

private readonly Dictionary<string, string> _headers = new Dictionary<string, string>(); 
private readonly List<string[]> _rows = new List<string[]>();

从ColdFusion c#重写之前的原始查询:

SELECT 
OA,
CD,
PD,               
sum(DS) as DS
sum(CS) as CS
FROM qDistinct
GROUP BY 
CD,
OA,
PD
ORDER BY 
ucaseCD,
OA,
PD

如果下面没有Sum的查询中GroupBy编码不一样，你能告诉我怎么做吗?

var Rows = allData.SelectMany(u => u._rows.Select(t => new
{
  OA = t[4],
  PD = t[0]      
}))
// Pseudo-code:
//.GroupBy(OA)
//.GroupBy(PD)   
.OrderBy(u => u.OA)
.ThenBy(u => u.PD)
.ToList();

Answer 1

你不太清楚你想要什么——像这样的东西?

var Rows = allData.SelectMany(u => u._rows.Select(t => new
{
  OA = t[4],
  CD = t[5],
  PD = t[0],
  DS = Convert.ToInt32(t[9]),
  CS = Convert.ToInt32(t[10])
}))
// group by the combination of CD, OA, and PD
.GroupBy(x => new { x.CD, x,OA, x.PD } )
// sum DS and CS within each group
.Select (g => new {g.Key.CD, 
                   g.Key.OA, 
                   g.Key.PD,  
                   DS = g.Sum(u=> u.DS), 
                   CS = g.Sum(u=> u.CS) 
                  } ) 
.OrderBy(u => u.CD)
.ThenBy(u => u.OA)
.ThenBy(u => u.PD)