3

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?

NMR
  • 166
  • 8

1 Answers1

3
qry = ctx.USER.Where(u => u.APPOINTMENTS
                           .Where(a => a.APPTYPEID == 1)
                           .Any(a => vals.Contains(a.COMPLETE)));

UPDATE (added returning those users, which do not have appointments at all)

qry = ctx.USER.Where(u => 
        !u.APPOINTMENTS.Any() ||
         u.APPOINTMENTS.Any(a => a.APPTYPEID == 1 && vals.Contains(a.COMPLETE)));
Sergey Berezovskiy
  • 232,247
  • 41
  • 429
  • 459
  • You can combine the `.Where()` and `.Any()` calls: `.Any(a => a.APPTYPEID == 1 && vals.Contains(a.COMPLETE))` – Bobson Feb 15 '13 at 20:07
  • @Bobson agree, that could be combined :) I'll leave it to the taste of Nicholas. Also, I'd check what SQL query is generated in both cases – Sergey Berezovskiy Feb 15 '13 at 20:13
  • Thanks for the quick response! This solution works well partially - if the USER has an appointment of type 1 which is complete, it returns those users. But if the users don't have any appointments of type 1 or at all, it doesn't return those if the list only contains "N" (which makes sense). I think I might need to split it into an IF/ELSE to capture this behavior, now that I think about it. – NMR Feb 15 '13 at 20:15
  • @NicholasRobert well, then I misunderstood your question, when you asking about app type *and* contains. Hold on, I'll modify answer – Sergey Berezovskiy Feb 15 '13 at 20:17