chl111 chl111 - 8 days ago 7
C# Question

Simple LINQ DataTable Aggregation Doesn't Work

So I have a dataset like this:

Group Value
A 1
A 2
B 2
B 5


Simply I just want to have:

Group Value
A 3
B 7


as another DataTable variable. Here is my C# code.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.OleDb;
using System.Globalization;
using System.IO;
using utilityClass;

namespace testData
{
class Program
{
static void Main(string[] args)
{

DataTable dt = new DataTable();
string path = "test_data.csv";
bool isFirstRowHeader = true;
dt = Utils.GetDataTableFromCsv(path, isFirstRowHeader);

var results = from row in dt.AsEnumerable()
group row by row.Field<string>("Group") into grp
select new
{
Id = grp.Key,
sum = grp.Sum(r => r.Field<int>("Value"))
};
DataTable newDataTbl = results.CopyToDataTable();
}
}
}


It gives me error in CopyToDataTable() saying:

enter image description here

Does anyone know what goes wrong in my codes? Thanks!

Answer

With a bit of slight of hand you can create a datarow collection in the from statement by using a where statement to assign the ItemArray of a datarow object and checking if it's null. Something like this:

static void Main(string[] args)
{

    DataTable dt = new DataTable();
    string path = "test_data.csv";
    bool isFirstRowHeader = true;
    dt = Utils.GetDataTableFromCsv(path, isFirstRowHeader);
    var results = from row in dt.AsEnumerable()
                  group row by row.Field<string>("Group") into grp
                  let dr = dt.NewRow()
                  where (dr.ItemArray = new object[] { grp.Key, grp.Sum(r => r.Field<int>("Value")) }) != null
                  select dr ;
    DataTable newDataTbl = results.CopyToDataTable();
}