title: Join between in memory collection and EntityFramework
body: <p>Is there any mechanism for doing a JOIN between an in-memory collection and entity framework while preserving the order.</p>
  
  <p>What I am trying is </p>
  
  <pre><code>var itemsToAdd = 
    myInMemoryList.Join(efRepo.All(), listitem =&gt; listitem.RECORD_NUMBER,
    efRepoItem =&gt; efRepoItem.RECORD_NUMBER, (left, right) =&gt; right);
  </code></pre>
  
  <p>which gives me the rather curiously titled "This method supports the LINQ to Entities infrastructure and is not intended to be used directly from your code." error.</p>
  
  <p>Now of course I can do this iteratively with something like</p>
  
  <pre><code>        foreach (var item in myInMemoryList)
          {
              var ho = efRepo.Where(h =&gt; h.RECORD_NUMBER == item.RECORD_NUMBER).FirstOrDefault();
              tmp.Add(ho);
          }
  </code></pre>
  
  <p>but this is an N+1 query. Which is nasty as myInMemoryList might be quite large!</p>
  
  <p>Resharper can refactor that for me to </p>
  
  <pre><code>        tmp = (from TypeOfItemInTheList item in myInMemoryList 
             select efRepo.Where(h =&gt; h.RECORD_NUMBER == item.RECORD_NUMBER)
             .FirstOrDefault());
  </code></pre>
  
  <p>which I suspect is still doing N+1 queries. So any ideas for a better approach to getting ef entities that match (on key field) with an in-memory collection. The resulting set must be in the same order as the in-memory collection was.</p>