title: Linq multiple where queries
body: <p>I have an issue building a fairly hefty linq query. Basically I have a situation whereby I need to execute a subquery in a loop to filter down the number of matches that are returned from the database. Example code is in this loop below:</p>
  
  <pre><code>        foreach (Guid parent in parentAttributes)
          {
              var subQuery = from sc in db.tSearchIndexes
                             join a in db.tAttributes on sc.AttributeGUID equals a.GUID
                             join pc in db.tPeopleIndexes on a.GUID equals pc.AttributeGUID
                             where a.RelatedGUID == parent &amp;&amp; userId == pc.CPSGUID                             
                             select sc.CPSGUID;
  
              query = query.Where(x =&gt; subQuery.Contains(x.Id));
           }
  </code></pre>
  
  <p>When I subsequently call the ToList() on the query variable it appears that only a single one of the subqueries has been performed and I'm left with a bucketful of data I don't require.  However this approach works:</p>
  
  <pre><code>       IList&lt;Guid&gt; temp = query.Select(x =&gt; x.Id).ToList();
  
          foreach (Guid parent in parentAttributes)
          {
              var subQuery = from sc in db.tSearchIndexes
                             join a in db.tAttributes on sc.AttributeGUID equals a.GUID
                             join pc in db.tPeopleIndexes on a.GUID equals pc.AttributeGUID
                             where a.RelatedGUID == parent &amp;&amp; userId == pc.CPSGUID                             
                             select sc.CPSGUID;
  
              temp = temp.Intersect(subQuery).ToList();
          }
  
          query = query.Where(x =&gt; temp.Contains(x.Id));
  </code></pre>
  
  <p>Unfortunately this approach is nasty as it results in multiple queries to the remote database whereby the initial approach if I could get it working would only result in a single hit. Any ideas?</p>