Efficient DataTable Group By

I would like to perform an aggregate query on a DataTable to create another DataTable. I cannot alter the SQL being used to create the initial DataTable.

Original DataTable: (everything is an int)

TeamID | MemberID
-------|-----------
1      | 1
1      | 2
1      | 3
2      | 4
2      | 5

Desired result:

TeamID | MemberIDCount
-------|--------------
1      | 3
2      | 2

If it were SQL I could just do

Select TeamID, Count(*) From Table Group By TeamID

but in my application, the only way I know how to handle this would be something like this:

Dictionary<int,int> d = new Dictionary<int,int>();
foreach (DataRow dr in dt.Rows)
{
    if (d.ContainsKey(dr.ID))
    {
        d[dr.ID] = d[dr.ID] + 1;
    }
    else
    {
        d.Add(dr.ID, 1);
    }
}

Is there a better way?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

You may use Linq.

var result = from row in dt.AsEnumerable()
              group row by row.Field<int>("TeamID") into grp
               select new
                 {
                 TeamID = grp.Key,
                  MemberCount = grp.Count()
                  };
 foreach (var t in result)
     Console.WriteLine(t.TeamID + " " + t.MemberCount);

Method 2

public DataTable GroupBy(string i_sGroupByColumn, string i_sAggregateColumn, DataTable i_dSourceTable)
{

    DataView dv = new DataView(i_dSourceTable);

    //getting distinct values for group column
    DataTable dtGroup = dv.ToTable(true, new string[] { i_sGroupByColumn });

    //adding column for the row count
    dtGroup.Columns.Add("Count", typeof(int));

    //looping thru distinct values for the group, counting
    foreach (DataRow dr in dtGroup.Rows) {
        dr["Count"] = i_dSourceTable.Compute("Count(" + i_sAggregateColumn + ")", i_sGroupByColumn + " = '" + dr[i_sGroupByColumn] + "'");
    }

    //returning grouped/counted result
    return dtGroup;
}

Example:

DataTable desiredResult = GroupBy("TeamID", "MemberID", dt);

Method 3

Group By in LINQ

var listInfo = (from infoMember in context.Members
                where infoMember.TeamID  == TeamID 
                group infoMember by new
                { infoMember.TeamID, infoMember.MemberIDCount } into newInfoMemeber
                select new ClassName
                {
                   TeamID = newInfo.Key.TeamID,
                   MemberIDCount = newInfo.Key.MemberIDCOunt,
                   Count = newInfo.Count(),
                   TotalCount = (from infoMemeber2 in context.Members
                                 where infoMemeber2.TeamID== TeamID
                                 select infoResult2).Count()
                }).AsEnumerable();

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0