CODEWITHSUNDEEP
c#linq
MAW74656
MAW74656

Reputation: 3539

How to convert linq result to DataTable

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?

Upvotes: 1

Views: 16161

Answers (4)

Dan Premkumar
Dan Premkumar

Reputation: 1

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>();

Upvotes: 0

Madhava Reddy
Madhava Reddy

Reputation: 39

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

Upvotes: 0

Spivonious
Spivonious

Reputation: 718

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);
}

Upvotes: 1

Olorin71
Olorin71

Reputation: 213

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.

Upvotes: 0

Related Questions