在内存集合和EntityFramework之间加入 [英] Join between in memory collection and EntityFramework

问题描述

我正在尝试的是

  var itemsToAdd = 
 myInMemoryList.Join（efRepo.All（），listitem => listitem.RECORD_NUMBER，
 efRepoItem = > efRepoItem.RECORD_NUMBER，（左，右）=>右）; 
  

这让我很好奇地标题为这种方法支持LINQ to Entities基础设施，而不是直接从您的代码使用。错误。

现在我可以用这样的方式迭代地执行这些操作，比如

  foreach（myInMemoryList中的var项）
 {
 var ho = efRepo.Where（h => h.RECORD_NUMBER == item.RECORD_NUMBER）.FirstOrDefault（）; 
 tmp.Add（ho）; 
} 
  

但这是一个N + 1查询。这是令人讨厌的，因为myInMemoryList可能相当大！

Resharper可以重构我，以便

  tmp =（fromInteMemoryList中的TypeOfItemInTheList项
选择efRepo.Where（h => h.RECORD_NUMBER == item.RECORD_NUMBER）
 .FirstOrDefault（））; 
  

我怀疑还在进行N + 1查询。所以任何想法，以更好的方法来获得与内存中的集合相匹配（关键字段）的ef实体。结果集必须与内存中集合的顺序相同。

解决方案

不能加入内存中的集合使用数据库结果集，而不将整个结果集加载到内存中，并使用linq对对象执行连接。尝试使用contains而不是join：

  var myNumbers = myInMemoryList.Select（i => i.RECORD_NUMBER）; 
 var itemsToAdd = efRepo.Where（e => myNumbers.Contains（e.RECORD_NUMBER））; 
  

这将生成具有 IN operator

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.

解决方案

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

这篇关于在内存集合和EntityFramework之间加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！