Linq-To-Entities 'Contains' 1-many condition
Consider the (simplified) table structure as follows:
[USERS]
- EmpId
- NAME
[DESTINATIONS]
- (FK_APPT_USER) EMPID
- APPTTYPEID
- FULL
Each user can have 0 .. * appointments, each of which can be one of many APPTYPEIDs and can be complete or incomplete.
I want to filter the result set of an IQueryable [USER] query so that it only includes USERS that have an apipt of some type of ID (say 1) and where the COMPLETE field is in the list of values. I do this as part of a gridview filter that allows users to choose to either show only completed or incomplete users for certain meeting 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
It's really easy to do this if the values I'm comparing against this list are in a 1-1 ratio with the USER object, like this:
var qry = ctx.USER.Where(x=> vals.Contains(x.NAME));
But I don't understand how to do this with a 1-many relationship like my appointment table, and it makes me brainwash trying to conceptualize its sql. Can anyone please explain how to do this?
source to share
qry = ctx.USER.Where(u => u.APPOINTMENTS
.Where(a => a.APPTYPEID == 1)
.Any(a => vals.Contains(a.COMPLETE)));
UPDATE (added to return those users who have no assignments at all)
qry = ctx.USER.Where(u =>
!u.APPOINTMENTS.Any() ||
u.APPOINTMENTS.Any(a => a.APPTYPEID == 1 && vals.Contains(a.COMPLETE)));
source to share