I’m attempting to run a LINQ query on a DataTable object, and I’m finding that it’s not as simple as it seems. Consider the following scenario:
var results = from myRow in myDataTable where results.Field("RowNo") == 1 select results;
This is not permitted. What’s the best way to get something like this to work?
I’m surprised LINQ queries aren’t supported on DataTables!
Asked by Calanus
Because DataRowCollection does not implement IEnumerableT>, you can’t query the DataTable’s Rows collection. For DataTable, you must use the AsEnumerable() extension. As follows:
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
IEnumerableDataRow> is returned by AsEnumerable(). Use the CopyToDataTable() extension to convert IEnumerableDataRow> to a DataTable.
A query containing Lambda Expression is shown below.
var result = myDataTable .AsEnumerable() .Where(myRow => myRow.Field<int>("RowNo") == 1);
Answered by Collin K
var results = from DataRow myRow in myDataTable.Rows where (int)myRow["RowNo"] == 1 select myRow
Answered by JoelFan
It’s not that they weren’t allowed on DataTables before the IQueryable and generic IEnumerable constructions, on which Linq queries can be run.
Both interfaces require type-safety validation of some sort. DataTables do not have a strong type system. This is why, for example, people are unable to query against an ArrayList.
To make Linq function, you must first map your results to type-safe objects and then query against them.
Answered by Jon Limjap
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
A project reference must also be added to System. Data.DataSetExtensions
Answered by Keith
I recognize this has been addressed previously, but I’d want to suggest a different approach:
I like to use the.CastT>() method because it keeps me sane by allowing me to see the explicit type defined. In any case, AsEnumerable() calls it:
var results = from myRow in myDataTable.Rows.Cast<DataRow>() where myRow.Field<int>("RowNo") == 1 select myRow;
var results = myDataTable.Rows.Cast<DataRow>() .FirstOrDefault(x => x.Field<int>("RowNo") == 1);
As mentioned in the comments, does not necessitate the use of System. Any other assemblies, such as Data.DataSetExtensions (Reference)
Answered by vandsh
Post is based on https://stackoverflow.com/questions/10855/linq-query-on-a-datatable