linq - 使用 LINQ 对多列进行分组和聚合

Question

我在尝试使用 LINQ 和 C# 从 csv 文件执行分组和计算行时遇到了麻烦。

(示例 csv)

T5,,929,,O,PPT-DAY,4,,,18.09,,ACXD:DSC,,,,,,,,,,,
T5,,929,,O,PPT-DAY,4,,,18.09,,ACXD:DSC,,,,,,,,,,,
T5,,644,,O,PPT-ANNUAL LEAVE,1,,,850.23,,INTERNAL,,,,,,,,,,,
T5,,900,,O,PPT-ANNUAL LEAVE,1,,,42.51,,INTERNAL,,,,,,,,,,,
T5,DEFAULT,674,,O,PPT-DAY,2,,,18.09,,ACXD:DSC,,,,,,,,,,,
T5,,674,,O,PPT-PERSONAL LEAVE-ON,8,,,19.9,,INTERNAL,,,,,,,,,,,

SQL 等价物

SELECT Col3  
      ,Col5
      ,Col6
      ,SUM(Col7) as [hours]     
      ,AVG(Col10) as [pay]    
      ,Col12     
  FROM mytable
  GROUP BY Col3, Col5, Col6, Col12
  ORDER BY Col3, Col5, Col6, Col12

我已经使用其他线程解决了这个问题，但我在多列分组和计算上挣扎。任何建议将不胜感激。

Answer 1

尝试这个，

var query = (from t in mytable
             orderby t.Col3, t.Col5, t.Col6, t.Col12
             group t by new {t.Col3, t.Col5, t.Col6, t.Col12} into grp
             select new
                {
                    grp.Key.Col3,
                    grp.Key.Col5,
                    grp.Key.Col6,
                    hours = grp.Sum(t => t.Col7),
                    pay = grp.Average(t => t.Col10),
                    grp.Key.Col12,
                }).ToList();

或者

   var query = mytable.GroupBy(t => new {t.Col3, t.Col5, t.Col6, t.Col12})
               .Select(grp => new
               {
                   grp.Key.Col3,
                   grp.Key.Col5,
                   grp.Key.Col6,
                   hours = grp.Sum(t => t.Col7),
                   pay = grp.Average(t => t.Col10),
                   grp.Key.Col12,
               })
               .OrderBy(t => new { t.Col3, t.Col5, t.Col6, t.Col12 }).ToList();