Sum rows (Group By) in a DataTable

Question

I have a DataTable like this

ProductId CountThisWeek CountLastWeek
        1            10            15         
        1            20             5

        2             5            10
        2            10            15
        2            10            20

        3            10            15

I need to obtain a new DataTable by "compressing"(sum by productId) my initial DataTable, like this :

ProductId CountThisWeek CountLastWeek
        1            30            20         
        2            25            45
        3            10            15

is there a way to do it (.NET 3.5) using LINQ or other techniques?

Answer 1

from r in table.AsEnumerable()
group r by r.Field<int>("ProductId") into g
select new {
   ProductId = g.Key,
   CountThisWeek = g.Sum(r => r.Field<int>("CountThisWeek")),
   CountLastWeek = g.Sum(r => r.Field<int>("CountLastWeek"))
}

You can use CopyToDataTable() method to create new DataTable from these anonymous objects.