Efficiente gruppo DataTable di

Vorrei eseguire una query aggregata su un DataTable per creare un altro DataTable. Non riesco a modificare l’SQL utilizzato per creare il DataTable iniziale.

DataTable originale: (tutto è un int)

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

Risultato desiderato:

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

Se fosse SQL, potrei farlo

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

ma nella mia applicazione, l’unico modo in cui so come gestirlo sarebbe qualcosa del genere:

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

C’è un modo migliore?

Puoi usare 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; } 

Esempio:

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

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