Replace DataTable RowFilter with LINQ

ADO.NET DataTable offers handy RowFilter property (via its DefaultView object), for example to filter by some string value and display filtered data in a grid a code like this is used:

odtMyData.DefaultView.RowFilter = "Field = 'value'"
odgDataGrid.DataSource = odtMyData

where odtMyData is a DataTable object and odgDataGrid is a Data Grid.

This works well, but what if you need preprocess data in the DataTable prior comparing it to the filter value – what if you need to apply a user-defined function to it? In my case I had a VB function called ProcessTags which stripped HTML tags from a string, so for example strings like this:

<a href="http://someurl">This is a sample text</a>

and this

<span style="color:red">This is a </span><label>sample text</label>

would be converted into the same text

This is a sample text

So I needed to create condition that would return DataTable rows with that have both HTML values by comparing to "This is a sample text".

How? RowFilter‘s syntax is pretty poor and external function cannot be used, so something like

odtMyData.DefaultView.RowFilter = "ProcessTags(Field) = 'This is a sample text'"
odgDataGrid.DataSource = odtMyData

would throw an error.

Enter LINQ. Using AsEnumerable DataTable extension and its WHERE clause you can use your own comparing function. First let’s define the function: it will accept a DataRow and string to compare its Field value to. The function will return TRUE if row satisfies the condition FALSE otherwise:

Function FilterDataRows(i_oRow As DataRow, i_sCompareString as string) as Boolean
   Return ProcessTags(i_oRow("Field")) = i_sCompareString
End Function

Since this function is a normal VB.NET function, I can use my own tag-stripping function ProcessTags in the comparasion operation. Now the only thing remains is to apply this to filter the DataTable:

odgDataGrid.DataSource = odtMyData.AsEnumerable.Where(Function(dr As DataRow) _
   FilterDataRows(dr, "This is a sample text")).AsDataView

There you have it. WHERE clause will call FilterDataRows function for every row in the DataTable which in turn perform comparasing using any logic limited only by your imagination and the result converted to DataView is bound to the DataGrid, displaying only filtered rows.

In the next post I will demonstrate how to use this technique to take filtering of Infragistics UltraWebGrid completely into your own hands, leaving nothing to chance.

Leave a Reply

Your email address will not be published. Required fields are marked *