Asp.NET DataTable query using linq

Question

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

Posting the error message would be a nice touch

Comfortably Numb
– Comfortably Numb

2013-09-27 15:26:05 +00:00
Commented Sep 27, 2013 at 15:26 — Comfortably Numb
– Comfortably Numb, Commented Sep 27, 2013 at 15:26

Tim Schmelter · Accepted Answer · 2013-09-27 15:33:38Z

1

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.

edited Sep 27, 2013 at 15:33

answered Sep 27, 2013 at 15:28

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Comfortably Numb · Accepted Answer · 2013-09-27 15:38:43Z

0

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/

answered Sep 27, 2013 at 15:38

Comfortably Numb

39.9k17 gold badges82 silver badges147 bronze badges

Collectives™ on Stack Overflow

Asp.NET DataTable query using linq

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related