Linq C# Groupby on datatable

Question

I would like to write a Linq query to apply group-by on DataTable, which outputs the result as a DataTable.

I tried this code, which returns a result as var type, but I want the result in DataTable

var query = from row in dt.AsEnumerable()
            group row by row.Field<DateTime>("ExpDate").Year into grp
            select new
            {
                Years = grp.Key,
                RSF = grp.Sum(r => r.Field<decimal>("SpaceRSF"))
            };

Please could you assist me in finding a proper answer.

I mean to say DataTable dts = from row in dt.AsEnumerable() group row by row.Field("ExpDate").Year into grp something like this not as var type, should be as DataTable type — user1619672, Nov 21 '12 at 11:24
Try that http://stackoverflow.com/a/1253737/1744834 if you want DataTable and not IEnumerable — Roman Pekar, Nov 21 '12 at 11:25

score 2 · Answer 1 · answered Nov 21 '12 at 11:26

Look at How to: Implement CopyToDataTable - this topic describes how to implement two custom CopyToDataTable extension methods that accept a generic parameter T of a type other than DataRow.

Just use code from that article (ObjectShredder<T>) to achieve following result:

DataTable table = query.CopyToDataTable();

score 0 · Answer 2 · answered Nov 21 '12 at 11:26

ok, so according to this

select new
            {
                Years = grp.Key,
                RSF = grp.Sum(r => r.Field<decimal>("SpaceRSF"))
            };

code which return result as var type effectively returns collection of objects having two properties: Years and RSF.
If you want to have datatable instead, then write the code that puts all the data into the datatable. Basically, it might look similarly as following:

DataTable table = new DataTable();
table.Columns.Add("Years");
table.Columns.Add("RSF");
foreach(var item in query)
{
     table.Add(item.Years, items.RSF)
}

Hope this helps.

thanks for answer, i have done same as you suggest but I think here in this case we may run extra foreach loop i want to avoid that foreach and get output directly as datatable if possible — user1619672, Nov 21 '12 at 11:32

Linq C# Groupby on datatable

2 Answers2