The article How to filter data with the ADO.NET DataView in VB.NET does not cover one of little-known features of the DataView to filter data rows based on relationships. For example, you need to display categories that contain more than five products, or you need to display customers who have bought more than 5 bank products. In each of these cases you have to filter one table based on the information in a related table. To create this sort of filter string, you need to combine:
– A table relationship which links two tables;
– A function such as AVG(), MAX(), MIN(), or COUNT() which aggregates data in the related records.
The next code lines show this approach in action:
‘ Define the relationship between Categories and Products.
Dim dtrel As New DataRelation(“CatProds”, ds.Tables(“Categories”).Columns(“CategoryID”), ds.Tables(“Products”).Columns(“CategoryID”))
‘ Add the relationship to the DataSet.
ds.Relations.Add(dtrel)
‘Filter the display of the Categories table using a filter expression based on the Products table
‘ The filter string you need is: MAX(Child(CatProds).UnitPrice) > 23
Dim view1 As New DataView(ds.Tables(“Categories”))
view1.RowFilter = “MAX(Child(CatProds).UnitPrice) > 23”
GridView1.DataSource = view1
The end result is that the GridView shows only the categories that have a product worth more than $23.