Datatable group by sum

Question

in a Queue i have datatables in the following format

some table in the Queue
    Name Rank
    AAA  9 
    BBB  5
    CCC  1
    DDD  5

some other table in the Queue
    Name Rank
    AAA  1 
    SSS  5
    MMM  1
    DDD  8

using LINQ need to process those tables table by table continously and add the results to a global DataTable in the following format:

Name  Rank1  Rank2  Rank3  Rank>3
AAA   1      0      0      1
BBB   0      0      0      1
CCC   1      0      0      0
DDD   0      0      0      2
SSS   0      0      0      1
MMM   0      0      0      0

in the global table 4 columns state how many times a name was ranked in ranks 1,2,3 or >3.

now if the name already exists in global table i will not add it but only increment the rank count columns, and if does not exist then add it.

i've done this with nested looping but i wonder if anyone can help me with the LINQ syntax to do such thing,also will using LINQ make the process faster than with nested looping?

note that new tables are added to the Queue every second and i will be getting sometable from the Queue and process it to the global datatable

Answer 1

table1.AsEnumerable().Concat(table2.AsEnumerable())
      .GroupBy(r => r.Field<string>("Name"))
      .Select(g => new {
          Name = g.Key,
          Rank1 = g.Count(x => x.Field<int>("Rank") == 1),
          Rank2 = g.Count(x => x.Field<int>("Rank") == 2),
          Rank3 = g.Count(x => x.Field<int>("Rank") == 3),
          OtherRank = g.Count(x => x.Field<int>("Rank") > 3)
      }).CopyToDataTable();

You will need implementation of CopyToDataTable method where Generic Type T Is Not a DataRow.

---

A little optimized solution (single parsing and single loop over grouped ranks):

(from row in table1.AsEnumerable().Concat(table2.AsEnumerable())
 group row by row.Field<string>("Name") into g
 let ranks = g.Select(x => x.Field<int>("Rank")).ToList()
 select new {
    Name = g.Key,
    Rank1 = ranks.Count(r => r == 1),
    Rank2 = ranks.Count(r => r == 2),
    Rank3 = ranks.Count(r => r == 3),
    OtherRank = ranks.Count(r => r > 3)        
 }).CopyToDataTable();