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