EntityFramework and in memory collection are linked.

c# entity-framework join linq linq-to-entities

Question

Is there a way to JOIN an in-memory collection with an entity framework while maintaining the order?

What I'm attempting is

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

resulting in the rather enigmatic title "This function supports the LINQ to Entities architecture and is not meant to be used directly from your code." error.

Now, naturally, I could perform this repeatedly using something like

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

This, however, is a N+1 question. Considering that myInMemoryList may be pretty extensive, this is bad.

I can refactor that using Resharper.

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

which, I believe, continues to run N+1 queries. Any suggestions for a better method of obtaining EF entities that match (on the key field) with an in-memory collection would be appreciated. The order of the resultant set must match that of the in-memory collection.

1
15
4/26/2016 11:05:42 PM

Accepted Answer

No, you cannot do a linq-to-objects join on an in-memory collection and a database result set without first loading the whole result set into memory. Try replacing the word connect with contains:

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

This will provide a query thatIN operator

18
7/1/2011 12:03:30 PM

Popular Answer

In this blog article, I explain how to do this using either stored procedures or the LINQKit's PredicateBuilder.

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



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow