Group by in DataTable

Question

I have a data table:

DataTable table = new DataTable();

table.Columns.Add("Name", typeof(string));
table.Columns.Add("Value", typeof(string));          

table.Rows.Add("A", "High");
table.Rows.Add("B", "Low");
table.Rows.Add("A", "Low");
table.Rows.Add("C", "High");
table.Rows.Add("B", "Medium");
table.Rows.Add("A", "High");
table.Rows.Add("A", "High");

I want to use LINQ to group my result like:

Name   value  Count
-------------------
A      High    3
A      Low     1
B      Medium  1
B      Low     1
C      High    1

Hello, and welcome to Stack Overflow. It's nice to hear that you want to group your table... but remember, this is a Q&A site. What is your question? What have you tried, where did you get stuck? — stakx - no longer contributing, Mar 04 '13 at 22:21

Sergey Berezovskiy · Answer 1 · 2013-03-04T22:30:07.443

This Linq to DataSet query will return grouped values as anonymous objects

var query = from r in table.AsEnumerable()
            group r by new { 
               Name = r.Field<string>("Name"),
               Value = r.Field<string>("Value")
            } into g
            select new {
                g.Key.Name,
                g.Key.Value,
                Count = g.Count()
            };

Usage:

foreach(var item in query)
{
    // item.Name
    // item.Value
    // item.Count
}

If you want result as another DataTable, then you can use CopyToDataTable extension as described in MSDN article How to: Implement CopyToDataTable Where the Generic Type T Is Not a DataRow:

DataTable result = query.CopyToDataTable();

score 0 · Answer 2 · answered Mar 04 '13 at 22:24

This is one way to do it:

IEnumerable<IGrouping<Tuple<string,string>, DataRow>> groups= table.Rows.OfType<DataRow>().GroupBy(x=> new Tuple<string,string>(x["Name"].ToString(), x["Value"].ToString()));

foreach (var group in groups)
{
    //Name Value: Count
    Console.WriteLine(group.Key.Item1 + " " + group.Key.Item2 + ": " + group.Count());
}

Group by in DataTable

2 Answers2