Join between in memory collection and EntityFramework

Question

Is there any mechanism for doing a JOIN between an in-memory collection and entity framework while preserving the order.

What I am trying is

var itemsToAdd = 
  myInMemoryList.Join(efRepo.All(), listitem => listitem.RECORD_NUMBER,
  efRepoItem => efRepoItem.RECORD_NUMBER, (left, right) => right);

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.

Now of course I can do this iteratively with something like

        foreach (var item in myInMemoryList)
        {
            var ho = efRepo.Where(h => h.RECORD_NUMBER == item.RECORD_NUMBER).FirstOrDefault();
            tmp.Add(ho);
        }

but this is an N+1 query. Which is nasty as myInMemoryList might be quite large!

Resharper can refactor that for me to

        tmp = (from TypeOfItemInTheList item in myInMemoryList 
           select efRepo.Where(h => h.RECORD_NUMBER == item.RECORD_NUMBER)
           .FirstOrDefault());

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.

Answer 1

No you cannot join in-memory collection with database result set without loading whole result set to the memory and performing the join with linq-to-objects. Try using contains instead of join:

var myNumbers = myInMemoryList.Select(i => i.RECORD_NUMBER);
var itemsToAdd = efRepo.Where(e => myNumbers.Contains(e.RECORD_NUMBER));

This will generate query with IN operator

Answer 2

You can read how you can do this with the PredicateBuilder from the LINQKit or Stored Procedures in my blog post.

http://kalcik.net/2014/01/05/joining-data-in-memory-with-data-in-database-table/