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. Any ideas how to get something like this working? I'm amazed that LINQ queries are not allowed on DataTables!

+3  A: 

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.

Jon Limjap

You can use LINQ to objects on the Rows collection, like so:

var results = from myRow in myDataTable.Rows where myRow.Field("RowNo") == 1 select myRow;
+11  A: 

You want what's known as LINQ to DataSet. That link will take you to the first in a series of posts introducing it on the ADO.NET team blog.

Matt Hamilton
+5  A: 

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

+53  A: 

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

AsEnumberable() returns IEnumerable<DataRow>. If you need to convert IEnumerable<DataRow> to a DataTable, use the CopyToDataTable() extension.

Collin K
You have a typo in there. Should be myRow.Field("RowNo")
VB Version: Dim results = From myRow In myDataTable.AsEnumerable _ Where myRow.Field("RowNo") = 1 _ Select myRow
The .Field() method should take a type like so: .Field<int>("RowNo"), shouldn't it?
Thanks, you're absolutely right. I've fixed the code.
Collin K
+1 following your guidance solved my problem.
Carlos Loth
+2  A: 
var results = from DataRow myRow in myDataTable.Rows
    where (int)myRow["RowNo"] == 1
    select myRow

hi, thanks for above discussion, it is really helping me a lot but i have a question. how will i select multiple columns here. I need two different columns, one is integer and other is string out of three columns of my datatable.


i have written this much so far--- var res = from DataRow myrow in dt.Rows where (string)myrow[2] == null select myrow.Field<string>("Cat_Name"), myrow.Field<Int64>("Cat_ID"); //["Cat_ID"],myrow["Cat_ID"]; but it is giving error
also can some one tell me how can i do proper formatting while sending message on this forum, it always removes the enter key pressed by methanks

var query = from p in dt.AsEnumerable() where p.Field("code") == this.txtCat.Text select new { name = p.Field("name"), age= p.Field("age")


Your answer were very useful for me. Thank you very much

This should be a comment, not a potential answer.