title: Combined query in linq
body: <p>I have two tables</p>
  
  <ol>
  <li>userfriendrequests</li>
  <li>userfriends</li>
  </ol>
  
  <p>I want to get all on my userfriendrequests data but exclude those userfriendrequests.UserID which are existing in my userfriends.FriendID list</p>
  
  <p>My ID is 1, and I want to retrieve my friend request.</p>
  
  <p>userfriendrequests-table
  the list of my friend request</p>
  
  <pre><code>UserID          FriendID
      2               1
      3               1
      4               1
      5               1
      6               1
      7               1
  </code></pre>
  
  <p>userfriends-table
  the list of my friends</p>
  
  <pre><code>UserID          FriendID
      1               22
      1               23
      1               33
      1               55
      1               2
      1               3
  </code></pre>
  
  <p>The result should be:</p>
  
  <pre><code>userfriendrequests
  UserID          FriendID
      4               1
      5               1
      6               1
      7               1
  </code></pre>
  
  <p>Now how can I do this in linq in a single query only?</p>
  
  <p>This is what I have tried so far:
  USING DOUBLE QUERY</p>
  
  <pre><code>List&lt;FriendRequest&gt; myFriendRequest = new List&lt;FriendRequest&gt;();
  using (dbasecoreEntities dbase = Logic.Utility.dbaseContext())
  {
      var ufr = (from uf in dbase.userfriendrequests
                 where uf.FriendID == details.UserID &amp;&amp; !uf.IsBlocked &amp;&amp; !uf.IsIgnored &amp;&amp; uf.UserID != details.UserID
                 select uf).OrderBy(i =&gt; i.RequestDate).Skip(skip).Take(take).ToList();
  
      var myfriends = (from frnd in dbase.userfriends where frnd.UserID == details.UserID select frnd.FriendID).ToList();
      ufr = ufr.Where(i =&gt; !myfriends.Any(j =&gt; j == i.UserID)).ToList();
  
      myFriendRequest = (from userFriend in ufr
                     select new FriendRequest(userFriend, details.GMTTimeZone)).ToList();
  }
  
  return myFriendRequest;
  </code></pre>
  
  <p>USING SINGLE QUERY. But this one takes a very very long time to return results.</p>
  
  <pre><code>using (dbasecoreEntities dbase = Logic.Utility.dbaseContext())
  {
      var list = (from uf in dbase.userfriendrequests.Where(i =&gt; i.FriendID == details.UserID &amp;&amp; !i.IsBlocked &amp;&amp; !i.IsIgnored &amp;&amp; i.UserID != details.UserID).AsEnumerable()
                  where !dbase.userfriends.Where(i =&gt; i.UserID == details.UserID).Any(j =&gt; j.UserID == uf.UserID)
                  select new FriendRequest(uf)).OrderBy(i =&gt; i.RequestDate).Skip(skip).Take(take).ToList();
  
      return list;
  }
  </code></pre>
  
  <p>In here I'm using AsEnumerable so I can directly use select new FriendRequest(uf)</p>
  
  <p>The DOUBLE QUERY takes less than 7 seconds for 10 records, and more than a minute for 100 records.</p>