title: Query Users who have all requested records in two tables
body: <p>Data structure looks like:</p>
  
  <p>User(id)</p>
  
  <p>UserApp(user_id, app_id)</p>
  
  <p>UserSkill(user_id, skill_id)</p>
  
  <p>Using linq-to-sql or EF, how would I construct a query to elegantly return only users who possess every requested app and skill? </p>
  
  <p>In addition, how would I adjust the query to return any user who possesses at least one of the requested apps or skills? Essentially an OR vs AND (above).</p>
  
  <p><strong>UPDATE 1:</strong></p>
  
  <p>So I think we're close. Basically I want to only return users who have ALL the requested apps and skills. If we have two arrays of requested ids for skills and apps:</p>
  
  <pre><code>int[] requestedAppIDs // [1, 2, 3]
  int[] requestedSkillIDs // [4, 5, 6]
  </code></pre>
  
  <p>I would only want to return a user if they have apps 1,2,3 AND skills 4,5,6.</p>
  
  <pre><code>var usersWithAllSelectedAppsAndSkills = 
      context.Users
      .GroupJoin(context.UserApp,
          k =&gt; k.id,
          k =&gt; k.user_id,
          (o, i) =&gt; new { User = o, UserApps = i })
      .GroupJoin(context.UserSkill,
          k =&gt; k.User.id,
          k =&gt; k.user_id,
          (o, i) =&gt; new { User = o.User, o.UserApps, UserSkills = i })
      .Where(w =&gt; !requestedAppIDs.Except(w.UserApps.Select(x =&gt; x.app_id).ToArray()).Any() &amp;&amp; !requestedSkillIDs.Except(w.UserSkills.Select(x =&gt; x.skill_id).ToArray()).Any())
      .Select(s =&gt; s.User)
      .ToList();
  </code></pre>
  
  <p>Obviously, LINQ does not know how to translate the UserSkills.Select().ToArray()'s in my Where() to SQL. How can I accomplish this? </p>
  
  <p>And, secondarily the OR solution as well (user has any one of the requested apps or skills). </p>