Query DataTable using LINQ in C#

LINQ is an excellent way for querying different data sources using C#.With LINQ queries we can query different data sources such as XML.Instead of using different querying languages such as XPath or SQL ,we can use C# regardless of the type of data source.
When using LINQ we need to be aware of just the LINQ query operators such as Select,Where.

LINQ queries can only be used with data sources which implements the IEnumerable <T> interface.This is a requirement for the data source to be queried using the linq queries.Most of the collections in .NET implements this interface ,so we can query most of the collections using linq.

Since ado.net datatable doesn’t implement this interface hence we can not use linq queries for querying the datatable directly.

If we try to access linq extension methods on the datatable directly ,we will see that there are no extension methods available for the datatable.

To query datatable using linq we call the AsEnumerable() method of the DataTable.Calling this method on the DataTable returns an object which implements the IEnumerable<T> interface.Now we can perform LINQ queries on this object.

To use this method we need to do the following

• Add a reference to the System.Data.DataSetExtensions.This is usually added by default.
• Add the namespace System.Linq

After adding the above namespace if we call the AsEnumerable() method on the datatable ,we are able to access LINQ extension methods on the datatable

In the following example we are creating a table having id and product as columns.These columns represents the id and product.We are adding different rows to the datatable having different id and product values.

Now we can perform the normal linq queries on the datatable.For example to search for a product with id 2 we can use the following linq query

static void Main(string[] args)
{
DataTable datatable = GetTable();
var dataRow=datatable.AsEnumerable().Where(x => x.Field<int>("Id") == 2).FirstOrDefault();
Console.WriteLine(dataRow["Product"]);
Console.ReadLine();
}

In this example we are using the FirstorDefault() method.

Instead of using the extension methods we can write the above query using the expression syntax as:

var dataRow = (from product in datatable.AsEnumerable()
where product.Field<int>("Id") == 2
select product).First();

Linq can help to perform complex queries on a datatable easily.We can use the different querying capabilities of the linq for performing different operations.For example we can project only few specific columns from a datatable using the select operation.The following operation will return only the product columns of the table.

 var dataRow = datatable.AsEnumerable().Select(x => new {Name=x.Field<int>("Product"),Date=DateTime.Now});

To convert the IEnumerable<DataRow> back to the DataTable we can use CopyToDataTable() method.

In the following query we are selecting the rows from the data table where Id is greater than 1 and then populating a new data table with the returned values.

 var dataRow = datatable.AsEnumerable().Where(x => x.Field<int>("Id") > 1);
 DataTable dt=dataRow.CopyToDataTable<DataRow>();