Advanced Filtering Data Question

From: Abelardo Vacca (Vacca_at_discussions.microsoft.com)
Date: 07/29/04 

Date: Thu, 29 Jul 2004 13:41:04 -0700

Hello,
I'm preparing to migrate our current application from VB6 to .NET. One of the issues we have is trying to show the programming team the advantages of using Datasets with tables instead of directly generating datasets with a single table with SQL commands [the closest thing to using a VB6 recordset], and so encapsulating all the DB related information into the Data Access Logic Components.

Today I've read about the classes DataView and DataViewManager to try to learn as much as I can on how to obtain the most out of Datasets and I came up with a question about data filtering:

<<Is there a way to reflect a filter on a table to a related table?>>

Allow me to explain myself:
Imagine I have a Database with two tables: Customers and Addresses, with a Relationship of 1 to many (where 1 Customer can have multiple Addresses.)
If I wanted to filter all the Customers that live at Metropolis("City" is a field on the table "Addresses"), the classic way yo go about this on VB6 would be to create a single Recordset with the filtered information using the two tables.

As I understand (according to the approach recommneded by the pattern & practices document : "Designing Data Tier Components and Passing Data Through Tiers") in .NET I would have a DAL Component for the Customers that would generate me a Dataset with two tables: Customers and Addresses. If the DALC programmer did not provide a function to perform the City filtering, what would be the best way to tackle this?

I supposed at first that a DataViewManager would do the trick, setting the RowFilter for the Table Addresses to "City = 'metropolis'" for instance. But this action only filters the Addresses DataView. Is there a way to propagate this filter to the Customer Dataview, so that the Customers DataView would only reflect those customers living at Metropolis?

Right now the best way I see to do this would be to generate a one-table-Dataset specific for this query, but if we are trying to keep the DB specifics from the higher layers programmers I don't see this as an elegant solution.

Thanks in advance, I hope I made sense.