How to convert linq result to DataTable

Question

I have the following DataTable:

DataTable itemsOnSkid = new DataTable();
itemsOnSkid.Columns.Add("ItemNumber");
itemsOnSkid.Columns.Add("Qty");

And I need to aggregate this datatable by itemnumber. I'm using the following linq code:

var result =    from row in itemsOnSkid.AsEnumerable()
                group row by row.Field<string>("ItemNumber") into grp
                select new
                {
                    ItemNumber = grp.Key,
                    Qty = grp.Sum(r => r.Field<int>("Qty"))
                };

Problem is that I need to replace the first datatable with this result, but I'm not able to use .CopyToDataTable() on result.

How can I convert this result back to a datatable?

@AlbinSunnanbo -Won't compile, and not available per intellisence. — MAW74656, Jul 29 '14 at 17:48
Have you included `using System.Data;` at the top of the file and added an reference to `System.Data.DataSetExtensions.dll`? — Albin Sunnanbo, Jul 29 '14 at 17:49
It seems that `CopyToDataTable` only works when `T` is as DataRow according to http://msdn.microsoft.com/en-us/library/bb396189(v=vs.110).aspx — Albin Sunnanbo, Jul 29 '14 at 18:06
@TFischer - `Error 2 'System.Collections.Generic.IEnumerable' does not contain a definition for 'CopyToDataTable' and the best extension method overload 'System.Data.DataTableExtensions.CopyToDataTable(System.Collections.Generic.IEnumerable)' has some invalid arguments...` — MAW74656, Jul 29 '14 at 18:09
possible duplicate of [LINQ query on a DataTable](http://stackoverflow.com/questions/10855/linq-query-on-a-datatable) — O.O, Jul 29 '14 at 18:56

Spivonious · Answer 1 · 2014-07-29T19:21:39.517

You can't use CopyToDataTable() in this case, since the thing you're copying from has to be a DataRow. I think you're stuck doing it manually.

DataTable items = new DataTable();
items.Columns.Add("number");
items.Columns.Add("qty");

var result = from r in items.AsEnumerable()
             group r by r.Field<string>("number") into grp
             select new {
                 number = grp.Key,
                 qty = grp.Sum(r => r.Field<int>("qty"))
             };

DataTable newItems = new DataTable();
newItems.Columns.Add("number");
newItems.Columns.Add("qty");

foreach (var item in result) {
    DataRow newRow = newItems.NewRow();
    newRow["number"] = item.number;
    newRow["qty"] = item.qty;
    newItems.AddRow(newRow);
}

score 0 · Accepted Answer · edited May 23 '17 at 11:45

I have tried fast the same as TFischer, but using directly a DataRow. No extra object.

This is my Code:

    private static void Main(string[] args)
    {
        var itemsOnSkid = CreateDataTable();

        FillData(itemsOnSkid);

        var result = itemsOnSkid.AsEnumerable().GroupBy(row => row.Field<string>("ItemNumber")).Select(
            grp =>
                {
                    var newRow = itemsOnSkid.NewRow();
                    newRow["ItemNumber"] = grp.Key;
                    newRow["Qty"] = grp.Sum(r => r.Field<int>("Qty"));
                    return newRow;
                }).CopyToDataTable();


    }

    private static DataTable CreateDataTable()
    {
        var itemsOnSkid = new DataTable();
        itemsOnSkid.Columns.Add("ItemNumber");
        itemsOnSkid.Columns.Add("Qty", typeof(int));
        return itemsOnSkid;
    }


    // Fill some Data in the Table
    private static void FillData(DataTable itemsOnSkid)
    {
        for (int i = 1; i <= 10; i++)
        {
            var newRow = itemsOnSkid.NewRow();
            newRow["ItemNumber"] = i % 3;
            newRow["Qty"] = i;
            itemsOnSkid.Rows.Add(newRow);
        }
    }

I Hope it helps.

I'm getting an error saying that `resultDatTable` does not exist. — MAW74656, Jul 29 '14 at 18:49
Oh sorry, I tipped it false. I've edited the answer with the right variable name. — Olorin71, Jul 29 '14 at 18:54

score 0 · Answer 3 · answered Jun 29 '15 at 07:34

0

 var query = from empl in te.Employees.AsEnumerable() select empl;
            List<Employee> dt = query.ToList();
            gdempdetails.DataSource = dt;
            gdempdetails.DataBind();

answered Jun 29 '15 at 07:34

Madhava Reddy

39
6

2

Provided code might help, but in order to make it a good answer you should also describe/explain why/how exactly does your code solves the problem. – ZygD Jun 29 '15 at 07:51

score 0 · Answer 4 · answered Mar 15 '22 at 21:15

Here's a simpler way to do this

// Create the table if you don't already have it 
// Otherwise ignore this part
DataTable newItems = new DataTable();
newItems.Columns.Add("number");
newItems.Columns.Add("qty");
    
// LINQ query
IEnumerable<DataRow> result =    from row in itemsOnSkid.AsEnumerable()
               group row by row.Field<string>("ItemNumber") into grp
               select newItems.LoadDataRow(new object[]
               {
                    ItemNumber = grp.Key,
                    Qty = grp.Sum(r => r.Field<int>("Qty"))
                }, false);

 // Copy rows to DataTable
 newItems = result.CopyToDataTable<DataRow>();

How to convert linq result to DataTable

4 Answers4