Group & Aggregate multiple columns using LINQ

Question

I'm getting in a mess attempting to perform grouping and computing rows from a csv file using LINQ and C#.

(sample 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 equivalent

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

I've had a few shots at working this out using other threads but am struggling on the multi column grouping and computations. Any suggestions would be much appreciated.

Show us your current LINQ code.

MarcinJuraszek
– MarcinJuraszek

2013-11-19 01:17:14 +00:00
Commented Nov 19, 2013 at 1:17 — MarcinJuraszek
– MarcinJuraszek, Commented Nov 19, 2013 at 1:17
How are you reading the CSV file?

Enigmativity
– Enigmativity

2013-11-19 02:33:26 +00:00
Commented Nov 19, 2013 at 2:33 — Enigmativity
– Enigmativity, Commented Nov 19, 2013 at 2:33

Prasad Kanaparthi · Accepted Answer · 2013-11-19 02:12:14Z

Try this,

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

OR

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

Collectives™ on Stack Overflow

Group & Aggregate multiple columns using LINQ

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related