On a DataTable, a LINQ query

Published: 12 February 2022

Category: c#

Problem

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

Solution #1

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

Solution #2

var results = from DataRow myRow in myDataTable.Rows
    where (int)myRow["RowNo"] == 1
    select myRow

Answered by JoelFan

Solution #3

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

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

A project reference must also be added to System. Data.DataSetExtensions

Answered by Keith

Solution #5

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