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

Solution

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

Comments

[user1590636]

but will this increment the ranks if the name already exists?

[Sergey Berezovskiy]

@user1590636 no, if name exists in both tables, then each group g will have two rows. Then ranks will have two values. E.g. for AAA group there will be values 1 and 9. Calculating counts will produce Rank1 = 1, Rank2 = 0, Rank3 = 0, OtherRank = 1

[Sergey Berezovskiy]

@user1590636 no problem :) Linq is a great thing

[user1590636]

but why don't they make the CopyToDataTable a built in thing at least for the Enumerable?!

[Sergey Berezovskiy]

@user1590636 agree with you, that thing should be built-in. Don't know why it exists only as article in msdn