Efficient DataTable Group By

Me gustaría realizar una consulta agregada en una DataTable para crear otra DataTable. No puedo alterar el SQL que se usa para crear la DataTable inicial.

DataTable original: (todo es un int)

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

Resultado deseado:

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

Si fuera SQL, podría hacer

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

pero en mi aplicación, la única forma en que sé cómo manejar esto sería algo como esto:

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

¿Hay una mejor manera?

Puedes usar Linq.

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

Ejemplo:

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

Agrupar por en 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();