Filtering rows by child data

[dgbailiff]

I need to perform some operations on rows that match a certain criteria. The challenge is, to find them by the properties in other tables. I have been using DataRelation objects and expression columns, but my latest hurdle is that I have child tables that don't allow me to look "downstream". The best way to explain this is with an example I suppose...

 

Using the Northwind sample database, imagine you need to locate every Employee for a given region. That means you have to locate an Employee with Territory.TerritoryDescription = 'something'. Ideally, it would be more user friendly if you could write code that was the equivalent of "give me all Employees who work in 'somewhere'". Even better, be able to write queries with boolean expressions for the WHERE component such that you could narrow down employees who work in (territoryA AND territoryB).

 

With SQL, I can write a query like...

 

SELECT Employees.EmployeeID, Territories.TerritoryDescription

FROM Employees, EmployeeTerritories, Territories

WHERE Employees.EmployeeID = EmployeeTerritories.EmployeeID AND EmployeeTerritories.TerritoryID = Territories.TerritoryID AND Territories.TerritoryDescription = "something";

 

I need to be able to go a step further and filter by multiple territories, as in...

 

SELECT Employees.EmployeeID, Territories.TerritoryDescription

FROM Employees, EmployeeTerritories, Territories

WHERE Employees.EmployeeID = EmployeeTerritories.EmployeeID AND EmployeeTerritories.TerritoryID = Territories.TerritoryID AND Territories.TerritoryDescription = "something1" OR Territories.TerritoryDescription = "something2";

 

Can someone please show me how to do the same thing in C# and ADO.NET? I don't care if it's a DataRow recordset or a DataView, I just need to be able to do this kind of query filtering.

[Nerseus]

I've generally built up my filter expression manually and then applied the filter using the "IN (...)". This would only work if the filter column (expression or not) is in your table you want to filter on - I can't tell from your description if that's the case or not.

 

If I hear you, you've got a many to many relationship (EmployeeTerritory being the join table). If your DataSet has a way to get the TerritoryID in the Employee DataTable (maybe an expression column), then you want to build up a string of TerritoryID's, something like:

DataRow[] rows = ds.Tables["Employee"].Select("TerritoryID IN (1, 3, 7)");

 

If you need help building up the "IN (...)" string, I can help.

 

If you don't have the TerritoryID in the employee table then you'll have to find a better way to do it. Here's what I would do in that case (and have done, in production code):

1. Get a list of TerritoryID's as above, but use it to filter on the EmployeeTerritory table:

2. Looping through the EmployeeTerritory records in step 1, get a list of EmployeeID's.

3. Use the list of Employee ID's in step 2 to filter the Employee table.

 

If you want a code example, let me know.

 

-ner

[dgbailiff]

I've generally built up my filter expression manually and then applied the filter using the "IN (...)". This would only work if the filter column (expression or not) is in your table you want to filter on - I can't tell from your description if that's the case or not.

 

If I hear you, you've got a many to many relationship (EmployeeTerritory being the join table). If your DataSet has a way to get the TerritoryID in the Employee DataTable (maybe an expression column), then you want to build up a string of TerritoryID's, something like:

DataRow[] rows = ds.Tables["Employee"].Select("TerritoryID IN (1, 3, 7)");

 

If you need help building up the "IN (...)" string, I can help.

 

If you don't have the TerritoryID in the employee table then you'll have to find a better way to do it. Here's what I would do in that case (and have done, in production code):

1. Get a list of TerritoryID's as above, but use it to filter on the EmployeeTerritory table:

2. Looping through the EmployeeTerritory records in step 1, get a list of EmployeeID's.

3. Use the list of Employee ID's in step 2 to filter the Employee table.

 

If you want a code example, let me know.

 

-ner

 

Thanks, Nerseus, for the quick reply.

 

No, the TerritoryID is not in the Employees table or this wouldn't be such a hassle. I can't create an expression field in the Employees table because EmpoyeeTerritories is a child table, and the expression field could only be an aggregate expression. My DB has this same exact situation which is why I chose this example. Your strategy seems to be a workable solution, so thanks for that idea. I think I can code that on my own.

 

Just out of curiosity, do you find that this kind of thing is a limitation or deficiency of ADO.NET? Or am I asking too much in wanting to be able to query/filter data with the same flexibility as SQL? :p It seems a shame to me that in these situations we programmers have to do backflips to do something simple...

[Nerseus]

I actually toyed around with the JoinView class - it's free code from MS, in VB.NET. I converted to C# and got it working fairly well, but haven't used it much. It allows creating a DataView like object called a JoinView that joins two tables into one - then you could filter across both tables. It's the equivalent of having a "SELECT table1.*, table2.* FROM table1 INNER JOIN table2 ON...".

 

You can have expression columns from a child table - the syntax is something like:

ds.Tables["Employee"].Columns["TerritoryID"].Expression = "child.TerritoryID";

 

That's from memory, so it may not be right. If you have multiple relationships you can do something like "child(EmployeeEmployeeTerritory).TerritoryID", where EmployeeEmployeeTerritory is the name of the relationship.

 

My luck with expression column wasn't that good. They worked well 99% of the time, but failed when you wanted to do HasChanges() or GetChanges() - they both want to build a NEW dataset (I guess) that only includes tables that had changes. If that was the parent table (Employee) but not a child table, then this would throw an exception because of the "child" expression column. I had to implement a scheme to remove expression columns temporarily and then reapply them when I was done - not too hard, but became tedious to remember and not very performant (it was generic code to loop through all tables/columns).

 

Good luck!

 

-nerseus

[dgbailiff]

Ah, good find! I'm short on time today, but I will check that out tomorrow.

 

FYI: I got the code working and it wasn't as complicated as I thought it would be. However, the deficiency I'm finding now is that it works only when the boolean expression is an OR, but I don't think it would work with an AND statement, such as "employee who works in both territory 1 and territory 2" or even worse "employees who work in (t1 AND t2) OR t3" or something complex like that. I haven't tested it yet, but my instinct says it won't work for the AND clauses. Which is a good reason to try the JoinView class.