LINQ query copytodatatable

Question

I have a linq query which is calculating particular data. Now I want that query to be typecasted to DataTable. Here is the query :

var query = dt.AsEnumerable()
    .GroupBy(row => new
    {
        Name = row.Field<string>("Name")
    })
    .Select(g => new
    {
        Name = g.Key.wcName,
        quantity = g.Count()
    });

I have heard about .CopyToDataTable which is used here but it is not showing. How can I convert the query to datatable ?

why do you use dt.AsEnumerable().GroupBy() not just dt.GroupBy() ? — Dennis C, May 06 '15 at 05:06
Refer http://stackoverflow.com/questions/4460654/best-practice-convert-linq-query-result-to-a-datatable-without-looping — captainsac, May 06 '15 at 05:07
No, it is giving error `'System.Data.DataTable' does not contain a definition for 'GroupBy' and no extension method 'GroupBy' accepting a first argument of type 'System.Data.DataTable' could be found` — Harshit, May 06 '15 at 05:09

score 3 · Accepted Answer · answered May 06 '15 at 05:25

3

First create a table with the schema, then Select with the result of IEnumerable<DataRow> in order to use CopyToDataTable()

var temp = new DataTable();
temp.Columns.Add("Name", typeof(string));
temp.Columns.Add("Quantity", typeof(int));

var query = dt.AsEnumerable()
    .GroupBy(row => row.Field<string>("Name"))
    .Select(g =>
    {
        var row = temp.NewRow();
        row.SetField("Name", g.Key);
        row.SetField("Quantity", g.Count());
        return row;
    }).CopyToDataTable();

answered May 06 '15 at 05:25

Eric

5,675
16
24

The new DataTable created is empty. No data is getting inserted in temp – Harshit May 06 '15 at 05:50
Add `temp.Rows.Add(row);` after `row.SetField("Quantity", g.Count());` will work – Harshit May 06 '15 at 05:56
@bogojane Opps, I should also edit the name of `query`. The variable `query` is the result DataTable already, not the `temp` – Eric May 06 '15 at 05:57

Gaurav Shivakumar · Answer 2 · 2015-05-06T05:48:11.253

public static class DataTableToListHelper
    {      
    public static List<T> DataTableToList<T>(this DataTable table) where T : class, new()
    {
        try
        {
            List<T> list = new List<T>();

            foreach (var row in table.AsEnumerable())
            {
                T obj = new T();

                foreach (var prop in obj.GetType().GetProperties())
                {
                    try
                    {
                        PropertyInfo propertyInfo = obj.GetType().GetProperty(prop.Name);
                        propertyInfo.SetValue(obj, Convert.ChangeType(row[prop.Name], propertyInfo.PropertyType), null);
                    }
                    catch
                    {
                        continue;
                    }
                }

                list.Add(obj);
            }

            return list;
        }
        catch (Exception ex)
        {                
            return null;
        }
    }
}
DataTable dt = new DataTable();
connection.Open();
adapter.Fill(dt);
connection.Close();
var entityObjectList = dt.DataTableToList<YOURENTITY>();

LINQ query copytodatatable

2 Answers2