Aggregate a Dataset

Question

How do i aggregate(using linq) the value of one column of a Dataset where the other column is the same.

For example, my columns are

Row1  Row2  

2     3  
4     5  
6     7  
2     2  
6     4  
7     4  
2     4

I need something like this

Row1 Row2  

2    9  
4    5  
6    11  
7    4

edit: the values in "Row2" are the number of "Row1". so the values of (for example: 2) have to be aggregated into a new datatable where is only a single entry for (2). I'm sorry if the question is vague.

Answer 1

You can use LINQ to aggregate rows and copy the results to a new table:

DataTable source = // whatever
DataTable dest = source.Clone();

var aggregate = source
    .AsEnumerable()
    .GroupBy(row => row.Field<int>("Row1"))
    .Select(grp => new { Row1 = grp.Key, Row2 = grp.Select(r => r.Field<int>("Row2")).Sum() });

foreach(var row in aggregate)
{
    dest.Rows.Add(new object[] { row.Row1, row.Row2 });
}

Answer 2

Add reference to System.Data.DataSetExtensions.dll

DataSet ds = ..
DataTable table = ds.Tables[0]; // or ds.Tables["YourTableName"];

var q = from row in table.AsEnumerable()
        group row by row.Field<int>("Row1") into g
        select new
        {
            Row1 = g.Key,
            Row2 = g.Sum(s => s.Field<int>("Row2"))
        };

If you need group by several columns:

var q = from record in table.AsEnumerable()
        group record by new { Row1 = record.Field<int>("Row1"), Row2 = record.Field<int>("Row3") } into g
        select new
        {
            Row1 = g.Key,
            Row2 = g.Sum(s => s.Field<int>("Row2"))
        };

Answer 3

If you don't (want to) use Linq you can try this Microsoft HOWTO and create your own helper class.