DataView Advanced Filtering with Relationships in C# And ASP.NET

The DataView allows for some surprisingly complex filter expressions. One of its little-known features is the ability to filter rows based on relationships. For example, you could display categories that contain more than 20 products, or you could display customers who have made a certain

number of total purchases. In both of these examples, you need to filter one table based on the
information in a related table.

To create this sort of filter string, you need to combine two ingredients:

• A table relationship that links two tables.
• An aggregate function such as AVG(), MAX(), MIN(), or COUNT(). This function acts on the
data in the related records.

For example, suppose you’ve filled a DataSet with the Categories and Products tables and
defined this relationship:

// Define the relationship between Categories and Products.
DataRelation relat = new DataRelation("CatProds",
ds.Tables["Categories"].Columns["CategoryID"],
ds.Tables["Products"].Columns["CategoryID"]);
// Add the relationship to the DataSet.
ds.Relations.Add(relat);

You can now filter the display of the Categories table using a filter expression based on the
Products table. For example, imagine you want to show only category records that have at least one
product worth more than $50. To accomplish this, you use the COUNT() function, along with the
name of the table relationships (CatProds).
Here’s the filter string you need:

MAX(Child(CatProds).UnitPrice) > 50

And here’s the code that applies this filter string to the DataView:

DataView view1 = new DataView(ds.Tables["Categories"]);
view1.RowFilter = "MAX(Child(CatProds).UnitPrice) > 50";
GridView1.DataSource = view1;

The end result is that the GridView shows only the categories that have a product worth more
than $50.