How to use linq query on a datatable in C#

Is it possible to perform a linq query on datatable, answer is no because datatable’s row collection, DataRowCollection does not implement IEnumerable so we cannot perform linq query on it. But if there is any way to convert our datatable data row collection into IEnumerable, then surely we can. In this article we will see how to convert a datatable inot IEnumerable and perform Linq query and finally convert the result to datatable.

So let's see the code, how can we use it.

DataTable dataTable = GetDataTable();
var result = from myRows in dataTable.AsEnumerable()
         where myRows.Field<String>("City").StartsWith("New")
         select myRows;

GetDataTable() is a method to get DataTable from database on which we use linq query to search our result,

Here is the method to get data from database into a DataTable

public DataTable GetDataTable()
{
  DataTable dataTable = new DataTable("Customers");
  using (SqlConnection conn = new 
  SqlConnection(ConfigurationManager.ConnectionStrings["dbConString"].ConnectionString))
  {
    SqlCommand cmd = conn.CreateCommand();
    cmd.CommandText = "Customer_Fetch";
    cmd.CommandType = CommandType.StoredProcedure;

    if (conn.State != ConnectionState.Open)
        conn.Open();

    SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
    dataTable.Load(dr);                
  }
  return dataTable;
}

Whatever we get into our result is in the form of EnumerableRowCollection, so how to convert it into a datatable? We can directly convert our result to DataView By using AsDataView method and finally to DataTable by using ToTable

DataTable newDataTable = result.AsDataView().ToTable();

Now we got back our result into a new DataTable so we can use it wherever we want.

Other blogs you may like

How to fill datatable from datareader in C# without dataadapter?

Most of the developer use DataAdapter to fill the DataSet and then take the first table from the data set, which is quite slow, why can't we use DataReader to read the data and use datatable's Load method to load the reader into it, in this article we will see how we can load a table faster and... By Alicia Gonzalez   On 09 Jun 2013  Viewed: 13,938

Convert datatable to json in C#

To convert a DataTable into JSON in C# is really very easy. Is there any class or method in .Net which can help us to directly do it, then answer is No. We need to write a small utility class or method which can help to convert any datatable into json easily.What we are going to do is, create a... By Ali Adravi   On 09 Jun 2013  Viewed: 31,182

Linq inner join, left outer join on two lists in C# with example

Joining two generic lists in linq is tricky, in this article we will see how we can join two lists by using inner join and then by using outer join. Using inner join is quite similar to sql inner join but outer join is a little different, so we will see it with example code as well as records... By Hamden   On 25 Sep 2013  Viewed: 39,590

Linq dynamic order by a list with example

Sort a list dynamically is easy, in this article we will create a class to sort the source dynamically by passing source, column and sort order. Let’s write our class and then we will see it with example, create a new class DynamicSort public static class DynamicSort { ... By Hamden   On 07 Sep 2013  Viewed: 5,624

Linq to Sql - Sort record on multiple columns in ascending or descending order

In SQL Server it's easy and we are familiar to sort orders on different columns but when it comes to Linq to SQL or Lamda expression we stuck how to sort the records, it is because we are experienced much. So let's discuss some daily use ordering of records with examples. **1**. Suppose we have... By Mike .Net   On 30 Jan 2013  Viewed: 4,915