title: Linq-To-Entities 'Contains' clause 1-many relationship
body: <p>Consider a (simplified) table structure like this:</p>
  
  <p>[USERS]    </p>
  
  <ul>
  <li>EMPID</li>
  <li>NAME</li>
  </ul>
  
  <p>[APPOINTMENTS]</p>
  
  <ul>
  <li>(FK_APPT_USER) EMPID</li>
  <li>APPTTYPEID</li>
  <li>COMPLETE</li>
  </ul>
  
  <p>Each user can have 0..* appointments, each of which can be one of many APPTYPEID's, and can either be complete or not complete. </p>
  
  <p>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.</p>
  
  <pre><code>List&lt;string&gt; vals = new List&lt;string&gt; {"Y","N"}
  //maybe the user has only selected Y so the above list only contains 1 element
  var qry = ctx.USER.Where(x=&gt; vals.Contains( ? ));
  //bind etc
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>var qry = ctx.USER.Where(x=&gt; vals.Contains(x.NAME)); 
  </code></pre>
  
  <p>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?</p>