Thursday, June 30, 2011

DataViews and Filter Expressions

Yesterday I helped a colleague with a problem involving a DataView that would not find any rows when given a valid filter expression that should have returned rows. It took a little while to figure the problem out, so I thought I’d post the solution in case anyone else is stuck with the same issue.

In our case we had a DataTable (as part of a DataSet) that contained rows. Using the Debug Visualiser for the DataSet we could view the rows and see the data we expected. We had a column called BranchId and we wanted to find rows with a specific value in this column. If we wrote our own loop (we’re working in .Net 2.0 so no LINQ etc.) to iterate all the rows, request the value of the BranchId column and check it against the value we were searching for (17 in the first case) we found the row no problem. However, if we created a DataView with a filter expression of “[BranchId] = 17” now rows were returned.

The usual culprit here is ‘RowState’, where you must tell the DataView whether to include added rows, deleted rows, or to look at the modified or original versions of the rows. However, our code was correctly telling the DataView which row states to check, and the row state for the rows in question matched, so this was not the problem. Interestingly if we called AcceptChanges on the DataSet then the view would work fine, but we couldn’t do this because we needed to retain the current row state value for later in our algorithm, and AcceptChanges resets the row state.

In the end it turned out the problem was caused by a failure to call ‘EndEdit’ on the row. Specifically, the BranchId value for these rows had been programmatically set via code earlier in the application and EndEdit hadn’t been called on the row afterwards. This meant the proposed value was 17, but the current and original values were‘unset’ (note, not actually null as requesting the original value threw an exception saying the value didn’t exist, even though nulls were allowed). Calling EndEdit on the row moved the value from ‘Proposed’ to the current value without affecting the RowState, and then the DataView filter worked fine.

Of course this all makes perfect sense when you think about it, and of course you should call EndEdit to commit changes to a DataRow when you’ve finished making them. The confusing issue in this case was that ONLY DataView was checking the ‘current’ value of the field. Everything else (the Debugger Visualiser and other debug tools, and any code we wrote to check the column value without specifying which value to check) was checking the proposed value by default. This made it appear like the DataView or filter expression was at fault, and there was no help, guidance or clear hint that the issue was caused by the same field having two different values simultaneously. Also, since most of our edits occur through controls like the DataGrid, EndEdit usually gets called for us by the control. It was only because we had (unusually) set the DataSet value programmatically that we needed to manually call EndEdit ourselves. Interestingly, there also doesn’t appear to be anyway to tell the DataView to check the proposed value rather than the current (committed) value.

So, moral of the story (and a good thing to remember anyway), always call EndEdit on your DataRows after you’ve programmatically made changes to them (and you want to keep/commit those changes in memory).


  1. Very helpful description on Filter expressions! Thanks dude! got what i was looking for so long!
    Free Software Download

  2. Post is nicely written and it contains many good things for me. I am glad to find your impressive way of writing the post. Now it become easy for me to understand and implement the concept

  3. I never would’ve thought about it this way unless it runs into your blog. Thanks for putting it up. I hope you have great success.