using linq on datatable and putting result back into datatable with same format

Question

What I m trying to do is relatively simple. I would like to use linq to compute some aggregated function on a group and then put the result back into a datatable of the same format. I did a lot of research and think I should use System.Data.DataSetExtensions and copy to datatable funtion. Here is my random datatable:

DataTable ADataTable = new DataTable("ADataTable");

        // Fake table data
        ADataTable.Columns.Add("PLANT", typeof(int));
        ADataTable.Columns.Add("PDCATYPE_NAME", typeof(int));
        ADataTable.Columns.Add("Month", typeof(int));
        ADataTable.Columns.Add("Year", typeof(int));
        ADataTable.Columns.Add("STATUS_NAME_REPORT", typeof(string));
        ADataTable.Columns.Add("SAVINGS_PER_MONTH", typeof(double));

        for (int i = 0; i < 15; i++)
        {
            for (int j = 1; j < 5; j++)
            {
                DataRow row = ADataTable.NewRow();

                row["PLANT"] = j;
                row["PDCATYPE_NAME"] = j;
                row["Month"] = DateTime.Now.Month;
                row["Year"] = DateTime.Now.Year;
                row["STATUS_NAME_REPORT"] = "Report";
                row["SAVINGS_PER_MONTH"] = j*i;

                ADataTable.Rows.Add(row);
            }
        }

Now I will clone this format and do a simple sum on it via linq:

 DataTable newtable = ADataTable.Clone();

        // The actual query
        IEnumerable<DataRow> query = (from rows in ADataTable.AsEnumerable()
                                      group rows by new
                                      {
                                          PLANT = rows.Field<int>("PLANT"),
                                          PDCATYPE_NAME = rows.Field<int>("PDCATYPE_NAME"),
                                          Month = rows.Field<int>("Month"),
                                          Year = rows.Field<int>("Year"),
                                          STATUS_NAME_REPORT = rows.Field<string>("STATUS_NAME_REPORT")
                                      } into g

                                      select new  
                                      {
                                           g.Key.PLANT,
                                           g.Key.PDCATYPE_NAME,
                                          g.Key.Month,
                                          g.Key.Year,
                                          g.Key.STATUS_NAME_REPORT,
                                          sum = g.Sum(savings => savings.Field<double>("SAVINGS_PER_MONTH")),
                                      });

        newtable = query.CopyToDataTable<DataRow>();

The LINQ works fine but as soon as I put IEnumarable DataRow in front I get error that I cannot convert anonymys type to datarow. But if I put select new datarow I get an error that fields are unknown...

How do I proceed please?

score 2 · Answer 1 · edited May 23 '17 at 12:14

You have multiple options, First is to use reflection to create a DataTable based on IEnumerable<T> and the other options is to populate your DataTable by enumerating your query like:

var query = ADataTable.AsEnumerable()
    .GroupBy(row => new
    {
        PLANT = row.Field<int>("PLANT"),
        PDCATYPE_NAME = row.Field<int>("PDCATYPE_NAME"),
        Month = row.Field<int>("Month"),
        Year = row.Field<int>("Year"),
        STATUS_NAME_REPORT = row.Field<string>("STATUS_NAME_REPORT")
    });

foreach (var g in query)
{
    newtable.LoadDataRow(new object[]
    {
                 g.Key.PLANT,
                 g.Key.PDCATYPE_NAME,
                 g.Key.Month,
                 g.Key.Year,
                 g.Key.STATUS_NAME_REPORT,
                g.Sum(savings => savings.Field<double>("SAVINGS_PER_MONTH"))
    }, LoadOption.OverwriteChanges);
}

The error in your code is because of selecting an anonymous type using select new and then trying to store it in IEnumerable<DataRow>. You can't specify DataRow in select as it is not accessible directly.

You may also see: How to: Implement CopyToDataTable Where the Generic Type T Is Not a DataRow

thanks. I just sorted it also, will add my way to the question — nik, Apr 01 '15 at 14:27
@nik, don't add/edit it to the question, instead add a separate *self* answer. It should be helpful for future visitors. — Habib, Apr 01 '15 at 14:28

nik · Accepted Answer · 2015-04-01T15:43:53.120

This also works:

newtable2 = ADataTable.AsEnumerable().GroupBy(a => new
{
    PLANT = a.Field<int>("PLANT"),
    PDCATYPE_NAME = a.Field<int>("PDCATYPE_NAME"),
    Month = a.Field<int>("Month"),
    Year = a.Field<int>("Year"),
    STATUS_NAME_REPORT = a.Field<string>("STATUS_NAME_REPORT")
}).Select(g =>
{
    var row = newtable2.NewRow();
    row.ItemArray = new object[]
            {
                g.Key.PLANT, 
                g.Key.PDCATYPE_NAME,
                g.Key.Month,
                g.Key.Year,
                g.Key.STATUS_NAME_REPORT,
                g.Sum(r => r.Field<double>("SAVINGS_PER_MONTH"))
            };
    return row;
}).CopyToDataTable();

using System.Data.DataSetExtensions (Which requires a reference)

using linq on datatable and putting result back into datatable with same format

2 Answers2