Group by on datatable by using linq query

debugcn Published at Dev

Prashant Khadatkar

Following are rows in my datatable:

TemplateName    Task                        Days
PT_Case     Assign Acceptance Task          83
PT_Case     Assign Acceptance Task          64
PT_Case     Assign Metadata Task            65
PT_Case     Assign Metadata Task            93  
PT_Type     Complete Metadata Task          67
PT_Type     Complete Metadata Task          -3
PT_Type     Assign Acceptance Task          67
PT_Type     Assign Acceptance Task          61

I need following output by linq query:

TemplateName         Task              Days  TotalDays
PT_Case    Assign Acceptance Task      73    152
PT_Case      Assign Metadata Task      79    152
PT_Type    Complete Metadata Task      32    96
PT_Type    Assign Acceptance Task      64    96

Note: "Days" calculated as average of task days. Please suggest, how to achieve it. Thanks in advance.

Sergey Berezovskiy

var query = from r in dt.AsEnumerable()
            group r by r.Field<string>("TemplateName") into templates
            let totalDays = templates.Sum(r => r.Field<int>("Days"))
            from t in templates
            group t by new
            {
                TemplateName = templates.Key,
                Task = t.Field<string>("Task"),
                TotalDays = totalDays
            } into tasks
            select new
            {
                tasks.Key.TemplateName,
                tasks.Key.Task,
                Days = tasks.Sum(r => r.Field<int>("Days")),
                tasks.Key.TotalDays
            };

It groups rows by template name and calculates total days for template. Then it groups template rows by task, and calculates days for tasks. Thats how I understand what you want to do. But result differs from your expected result (I don't understand how you get those values):

 TemplateName         Task              Days  TotalDays
    PT_Case    Assign Acceptance Task   147   305
    PT_Case      Assign Metadata Task   158   305
    PT_Type    Complete Metadata Task    64   192
    PT_Type    Assign Acceptance Task   128   192

Collected from the Internet

Please contact [email protected] to delete if infringement.