Consider a (simplified) table structure like this:
[USERS]
- EMPID
- NAME
[APPOINTMENTS]
- (FK_APPT_USER) EMPID
- APPTTYPEID
- COMPLETE
Each user can have 0..* appointments, each of which can be one of many APPTYPEID's, and can either be complete or not complete.
I want to filter the result set of a IQueryable[USER] query such that it only includes USERS who have an appt of some typeID (say 1) and where the COMPLETE field is in a list of values. I'm doing this as part of a gridview filter that allows users to select either to show only completed or not completed users for particular appointment types.
List<string> vals = new List<string> {"Y","N"}
//maybe the user has only selected Y so the above list only contains 1 element
var qry = ctx.USER.Where(x=> vals.Contains( ? ));
//bind etc
This is really easy to do if the values I'm comparing against the list are in a 1-1 relationship with the USER object, for example:
var qry = ctx.USER.Where(x=> vals.Contains(x.NAME));
But I don't understand how to do it with a 1-many relationship like with my appointments table, it's getting me all brain-tied trying to conceptualize the entity sql for it. Can anybody explain how to do this?