I've been developing a Windows Form application in VB.NET, and came across an interesting puzzle when trying to filter the contents of a ListBox based on a value in a child record.
To explain a bit more, I have a DataSet that contains a table with records of courses and a related table of qualifications. A course can have more than one qualification. I have a DataView based on the course table, and wanted to filter it based on a selected qualification. The RowFilter property of the DataView allows you to select a child row using Child.Row or Child(Relationship).Row in the expression. But to quote the documentation: 'because child relationships may return multiple rows, you must include the reference to the child column in an aggregate function.'. I hoped that I could therefore do something like:
Count(Child.qualification='Masters')>0
Sadly, no. This simlpy returns the following error:
"Syntax error in aggregate argument: Expecting a single column argument with possible 'Child' qualifier."
So, it appeared that you can't apply a filter to a Child element in the filter expression. My solution to the problem was to add a new boolean column to the course DataTable, and then update the value of it depending on the selected value using an XPath query. It does mean that you have to reset the values of the column each tmie the filter value is changed, but for this application that approach appears to work fine, without any major performance penalties.
Hopefully, though, this will be something that's looked at in a future version of .net...