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 (we are assuming you all know what LINQ and Lambda Expressions is, it might be covering these in a later post).
So let’s move into some action here. We are creating a console application. There will be a data table with some values in it and will have another collection which is going to be used for filtering my data table with.
The following helper method 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 have some data added.
Here's a List of type string, which will contain some “DepartmentId”s. The requirement is to filter a data table where it will only have rows where the "DepartmentId" is in the List.
So this is the collection and datatable.
List<string> deptList = new List<string>() { "DEPT1", "DEPT3" };
DataTable dt = PopulateDataTable();
Now comes the interesting part. We want to get the rows where the "DepartmentId" is "DEPT1" or "DEPT3". This is the code to filter the data table from values in the collection.
dt = dt.AsEnumerable()
.Where(dr => deptList.Contains(dr["DepartmentId"].ToString()))
.CopyToDataTable();
Let's show you the output first.
 |
| Output |
It is just a one line in the code up there. We 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 let us do what we have done here.
The extension method AsEnumerable() returns an IEnumerable<T> object, where the T is a DataRow. And then filtering all rows using a Lambda Expression. Finally, calling CopyToDataTable() extension method 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. The full sample is in MSDN Code Gallery and hope you will find this interesting.
Happy Coding.