Visual C#: Filter a DataTable Based on a Collection

Visual C#: Filter a DataTable Based on a Collection

This article is if you have a requirement where you want to filter the rows in a data table based on some values in a collection. So this is one way to do it.

This will really show you the power of LINQ and Lambda Expressions (I am assuming you all know what LINQ and Lambda Expressions is, I might be covering these in a later post).

So let’s move into some action here. I am creating a console application. There I will have a data table with some values in it and I will have another collection which I am going to use for filtering my data table with.

I have following helper method which will return a data table with some values.
static DataTable PopulateDataTable()
{
    DataTable dtEmployee = new DataTable();
    dtEmployee.Columns.Add("EmpId", typeof(int));
    dtEmployee.Columns.Add("FirstName", typeof(string));
    dtEmployee.Columns.Add("LastName", typeof(string));
    dtEmployee.Columns.Add("DepartmentId", typeof(string));

    dtEmployee.Rows.Add(1, "Jaliya", "Udagedara", "DEPT1");
    dtEmployee.Rows.Add(2, "John", "Doe", "DEPT2");
    dtEmployee.Rows.Add(3, "Jane", "Doe", "DEPT3");
    dtEmployee.Rows.Add(4, "John", "Smith", "DEPT4");
    dtEmployee.Rows.Add(5, "Jane", "Smith", "DEPT1");
    return dtEmployee;
}
Actually there is nothing much to describe here, a simple data table with some columns and I have some data added.

Now I have a List of type string, which will contain some “DepartmentId”s. My requirement is I want to filter up my data table where it will only have rows where the "DepartmentId" is in my List.

So this is my collection and my datatable.
List<string> deptList = new List<string>() { "DEPT1", "DEPT3" };
DataTable dt = PopulateDataTable();
Now comes the interesting part. I want to get the rows where the "DepartmentId" is "DEPT1" or "DEPT3". This is the code to filter up my data table from values in the collection.
dt = dt.AsEnumerable()
    .Where(dr => deptList.Contains(dr["DepartmentId"].ToString()))
    .CopyToDataTable();
Let me show you the output first.

Output

It is just a one line in the code up there. I have used LINQ (method syntax) with Lambda Expressions. In here both the AsEnumerable() and CopyToDataTable() are two extension methods of the DataTable class. Actually these methods are the two key points which did let me do what I have done here.

The extension method AsEnumerable() returns an IEnumerable<T> object, where the T is a DataRow. And then I am filtering all my rows using a Lambda Expression. Finally calling CopyToDataTable() extension method I am returning a DataTable.

What CopyToDataTable() does is when we supply IEnumerable<T> where the T is a DataRow, it will return a DataTable that contains set of DataRows.

So that’s it. I am uploading the full sample to MSDN Code Gallery and hope you will find this interesting.
   Download Sample

Happy Coding.
Sort by: Published Date | Most Recent | Most Useful
Comments
  • Jaliya Udagedara edited Original. Comment: updated