Filter a DataTable based on a Collection

Today I had a requirement where I want to filter the rows in a data table based on some values in a collection. So this is how I managed to do it and thought to share with you all.

The reason for me to share this is, 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 to 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 contain set of DataRows.

So that’s it. I am uploading the full sample to my SkyDrive and hope you will find this interesting.

Happy Coding.

Regards,
Jaliya