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

what m trying relatively simple. use linq compute aggregated function on group , put result datatable of same format. did lot of research , think should use system.data.datasetextensions , copy datatable funtion. here 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));          (int = 0; < 15; i++)         {             (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 clone format , simple sum on via linq:

 datatable newtable = adatatable.clone();          // actual query         ienumerable<datarow> query = (from rows in adatatable.asenumerable()                                       group rows 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")                                       } 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 put ienumarable datarow in front error cannot convert anonymys type datarow. if put select new datarow error fields unknown...

how proceed please?

you have multiple options, first use reflection create datatable based on ienumerable<t> , other options populate datatable enumerating 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 code because of selecting anonymous type using select new , trying store in ienumerable<datarow>. can't specify datarow in select not accessible directly.

you may see: how to: implement copytodatatable generic type t not datarow