Feb 23, 2016

Query DataTable using LINQ in C#

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.
Using Linq queries on datatable
To query datatable using linq we call the AsEnumerable() method of the DataTable.Calling this method on theDataTable 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
Using Linq queries  in 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.
Querying datatable using LINQ
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
Instead of using the extension methods we can write the above query using the expression syntax as:
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 columns from a table using the select operation.The following operation will return only the product columns of the table.
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.


Post a Comment

Nam Le © 2014 - Designed by Templateism.com, Distributed By Templatelib