Linq query to sum by group

Question

I have a data table like this:

Category         Description       CurrentHours      CTDHours  
LC1              Cat One                 5               0  
LC2              Cat Two                 6               0  
LC3              Cat Three              18               0  
LC1              Cat One                 0               9  
LC2              Cat Two                 0              15  
LC4              Cat Four                0              21  

That I need to Group and Sum to this:

Category         Description       CurrentHours      CTDHours  
LC1              Cat One                 5              14  
LC2              Cat Two                 6              21  
LC3              Cat Three              18               0  
LC4              Cat Four                0              21  

In other words I need to sum the two Hours columns grouping by the Category and Description columns.

I know that I could build a new table and loop through the existing data and sum the data into the new table but I thought there would be an easier way to do it using Linq. I've googled it for a few hours but all the examples I found didn't seem to fit what I was trying to do.

BTW, the odbc driver that creates the data table does not have the capability for sub queries, etc. or I would have just done it using SQL.

Chlorous

Answer 1

Use anonymous object to group by category and description. Here is Linq to DataSet query which returns grouped hours:

from r in table.AsEnumerable()
group r by new { 
     Category = r.Field<string>("Category"),
     Description = r.Field<string>("Description")
} into g
select new {
   Category = g.Key.Category,
   Description = g.Key.Description,
   CurrentHours = g.Sum(x => x.Field<int>("CurrentHours"),
   CTDHours = g.Sum(x => x.Field<int>("CurrentHours") + x.Field<int>("CTDHours"))
} 

If you are querying database (not clear from question):

from r in context.Table
group r by new { 
     r.Category,
     r.Description
} into g
select new {
   g.Key.Category,
   g.Key.Description,
   CurrentHours = g.Sum(x => x.CurrentHours),
   CTDHours = g.Sum(x => x.CTDHours + x.CurrentHours)
}

Eccentricity

That worked. Thanks so much. BTW, how did you get my columns to line up so perfectly? I tried to figure it out and failed. – Chlorous

@Chlorous I selected tables and formatted as code (it preserves whitespaces) :) – Eccentricity

@SergeyBerezovskiy thanks for giving both versions,** truly amazing** :) – Contingent

Answer 2

You need to sum CurrentHours and CTDhours, so -

select new {
   ...
   CTDHours = g.Sum(x => x.Field<int>("CTDHours") + g.Sum(x => x.Field<int>("CurrentHours")
}

Paronomasia