LINQ query on a DataTable
Solution 1
You can't query against the DataTable
's Rows collection, since DataRowCollection
doesn't implement IEnumerable<T>
. You need to use the AsEnumerable()
extension for DataTable
. Like so:
var results = from myRow in myDataTable.AsEnumerable()
where myRow.Field<int>("RowNo") == 1
select myRow;
And as @Keith says, you'll need to add a reference to System.Data.DataSetExtensions
AsEnumerable()
returns IEnumerable<DataRow>
. If you need to convert IEnumerable<DataRow>
to a DataTable
, use the CopyToDataTable()
extension.
Below is query with Lambda Expression,
var result = myDataTable
.AsEnumerable()
.Where(myRow => myRow.Field<int>("RowNo") == 1);
Solution 2
var results = from DataRow myRow in myDataTable.Rows
where (int)myRow["RowNo"] == 1
select myRow
Solution 3
It's not that they were deliberately not allowed on DataTables, it's just that DataTables pre-date the IQueryable and generic IEnumerable constructs on which Linq queries can be performed.
Both interfaces require some sort type-safety validation. DataTables are not strongly typed. This is the same reason why people can't query against an ArrayList, for example.
For Linq to work you need to map your results against type-safe objects and query against that instead.
Solution 4
As @ch00k said:
using System.Data; //needed for the extension methods to work
...
var results =
from myRow in myDataTable.Rows
where myRow.Field<int>("RowNo") == 1
select myRow; //select the thing you want, not the collection
You also need to add a project reference to System.Data.DataSetExtensions
Solution 5
I realize this has been answered a few times over, but just to offer another approach:
I like to use the .Cast<T>()
method, it helps me maintain sanity in seeing the explicit type defined and deep down I think .AsEnumerable()
calls it anyways:
var results = from myRow in myDataTable.Rows.Cast<DataRow>()
where myRow.Field<int>("RowNo") == 1 select myRow;
or
var results = myDataTable.Rows.Cast<DataRow>()
.FirstOrDefault(x => x.Field<int>("RowNo") == 1);
As noted in comments, does not require System.Data.DataSetExtensions
or any other assemblies (Reference)
Calanus
c# and SAS (!) programmer from the UK, working in the life-sciences area
Updated on July 08, 2022Comments
-
Calanus almost 2 years
I'm trying to perform a LINQ query on a DataTable object and bizarrely I am finding that performing such queries on DataTables is not straightforward. For example:
var results = from myRow in myDataTable where results.Field("RowNo") == 1 select results;
This is not allowed. How do I get something like this working?
I'm amazed that LINQ queries are not allowed on DataTables!