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.

Solution 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

Solution 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/

Solution 3

try this:

var list = (from n in efRepo
           where myInMemoryList.Select(m=>m.RECORD_NUMBER).Contains(n.RECORD_NUMBER)
           select n).ToList();

Contains will be translated to IN operator in SQL (only if your RECORD_NUMBER member is a primitive type like int, string, Guid, etc)

Comments

[Andiih]

I can't put the EFRepo records into memory - its way too big.

[Yann Olaf]

ok. Than perhaps a solution could be that you use the CONTAINS operator. But remember the sql server supports only a limmited number of elements for "IN" expressions. Perhaps you have to chunk you requests.

[Andiih]

I've just tried this and it appears to work for reasonably large data sets. Is @Olafs fears on the limited number of elements for an IN clause justified, or does EF take care of that ?

[Ladislav Mrnka]

Yes IN is not for passing hundreds of record numbers. When you build linq query always think about how would you solve the problem with common SQL? How would you join your in memory data with data in database with SQL? You would not unless you first insert in-memory data to temporary table or use stored procedure with table valued parameter (or some special handling with passing data as string parameter and parsing it on SQL server). EF will do nothing of that for you so you can either use contains with smaller data sets or create stored procedure for that.

[Andiih]

thx - this will work for now but I think I may need to refactor this totally with temp tables.

[Andiih]

Thanks - Very useful.

[Søren Boisen]

Great blog post! The most useful performance comparison I could find.