How do I GroupBy one column on this DataTable

Question

Suppose I have a call log DataTable where each row represents a call placed with the following columns: AccountNumber1, AccountNumber2, AccountListDate, AccountDisposition

I want to GroupBy column AccountNumber1 and want a new DataTable with the same columns + 1 additional column NumCalls which will be the count of calls for each AccountNumber1.

New DataTable after GroupBy: AccountNumber1, AccountNumber2, AccountListDate, AccountDisposition, NumCalls

So far I have the following:

table.AsEnumerable()
    .GroupBy(x => x.Field<int>("AccountNumber1"))
    .Select(x => new { x.Key.AccountNumber1, NumCalls = x.Count() })
    .CopyToDataTable()

Which gives me a DataTable with just two columns AccountNumber1 and NumCalls. How do I get the other columns as I described above?? I would appreciate any help. Thank you.

Answer 1

There's no magic, you need to use a loop and initialize the new table with the new column:

DataTable tblResult = table.Clone();
tblResult.Columns.Add("NumCalls", typeof(int));
var query = table.AsEnumerable().GroupBy(r => r.Field<string>("AccountNumber1"));
foreach (var group in query)
{
    DataRow newRow = tblResult.Rows.Add();
    DataRow firstOfGroup = group.First();
    newRow.SetField<string>("AccountNumber1", group.Key);
    newRow.SetField<string>("AccountNumber2", firstOfGroup.Field<string>("AccountNumber2"));
    newRow.SetField<DateTime>("AccountListDate", firstOfGroup.Field<DateTime>("AccountListDate"));
    newRow.SetField<string>("AccountDisposition", firstOfGroup.Field<string>("AccountDisposition"));
    newRow.SetField<int>("NumCalls", group.Count());
}

This takes arbitrary values from the first row of each group which seems to be desired.