Filtering rows by child data

D

dgbailiff

New member
Joined
Aug 17, 2005
Messages
3
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 dont 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 dont care if its a DataRow recordset or a DataView, I just need to be able to do this kind of query filtering.
 
Ive 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 cant tell from your description if thats the case or not.

If I hear you, youve 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 TerritoryIDs, something like:
C#: 
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 dont have the TerritoryID in the employee table then youll have to find a better way to do it. Heres what I would do in that case (and have done, in production code):
1. Get a list of TerritoryIDs as above, but use it to filter on the EmployeeTerritory table:
2. Looping through the EmployeeTerritory records in step 1, get a list of EmployeeIDs.
3. Use the list of Employee IDs in step 2 to filter the Employee table.

If you want a code example, let me know.

-ner
 
Nerseus said:
Ive 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 cant tell from your description if thats the case or not.

If I hear you, youve 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 TerritoryIDs, something like:
C#: 
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 dont have the TerritoryID in the employee table then youll have to find a better way to do it. Heres what I would do in that case (and have done, in production code):
1. Get a list of TerritoryIDs as above, but use it to filter on the EmployeeTerritory table:
2. Looping through the EmployeeTerritory records in step 1, get a list of EmployeeIDs.
3. Use the list of Employee IDs in step 2 to filter the Employee table.

If you want a code example, let me know.

-ner
Click to expand...

Thanks, Nerseus, for the quick reply.

No, the TerritoryID is not in the Employees table or this wouldnt be such a hassle. I cant 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...
 
I actually toyed around with the JoinView class - its free code from MS, in VB.NET. I converted to C# and got it working fairly well, but havent 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. Its 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:
C#: 
ds.Tables["Employee"].Columns["TerritoryID"].Expression = "child.TerritoryID";

Thats 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 wasnt 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
 
Ah, good find! Im short on time today, but I will check that out tomorrow.

FYI: I got the code working and it wasnt as complicated as I thought it would be. However, the deficiency Im finding now is that it works only when the boolean expression is an OR, but I dont 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 havent tested it yet, but my instinct says it wont work for the AND clauses. Which is a good reason to try the JoinView class.
 
Back
Top