CODEWITHSUNDEEP
asp.netlinqdatatablegroup-by
tulkas85
tulkas85

Reputation: 1103

Asp.NET DataTable query using linq

I need to do this query in linq on a DataTable object

Select * from mydatatable m where m.id==myid group by myField

If I try this code I have an error on CopyToDataTable() 

int _idOrg=10;
DataTable dtAbb = (DataTable)ViewState["dtAbb "];
            var var_sez = from myRow in dtAbb .AsEnumerable()
                              group myRow by myRow.Field<string>("ID_IST") into newRow
                              where newRow.First().Field<int>("ID_ORG") == _idOrg
                              select newRow;
 DataTable dtSez = null;
 if (var_sez.Count() > 0)
     dtSezione = var_sez.AsEnumerable().CopyToDataTable();

Upvotes: 0

Views: 1555

Answers (2)

suff trek
suff trek

Reputation: 39777

If you want to use a ToDataTable on your query result - you have to extend your LINQ a little.

"LINQ in Action" book has a nice code companion available here. Download it, locate ObjectShreder.cs and DataSetLinqOperators.cs include them into your project and the methods become available to types other than IEnumerable<DataRow>

Reference: http://codecorner.galanter.net/2009/12/17/grouping-ado-net-datatable-using-linq/

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460108

You cannot use CopyToDataTable on a seqence of groups of DataRows but only on IEnumerable<DataRow>. So you either have to take all rows from each group(but why do you group then at all) or use a specific row of each group. Here i take simply the first:

var var_sez = from myRow in dtAbb.AsEnumerable()
              where myRow.Field<int>("ID_ORG") == _idOrg
              group myRow by myRow.Field<string>("ID_IST") into grp
              select grp.First();

Note that i've also moved the Where before the GroupBy. You are selecting only groups where the first row's ID matches the given ID. I assume you wahnt only rows with this ID. Hence this approach is "more corect" and more efficient.

Upvotes: 1

Related Questions