linq到实体框架：在查询中使用字典 [英] linq to entity framework: use dictionary in query

问题描述

我有：

 字典< int，List< int>> dict = new ... 
 var a = SomeEntity.Where（f => dict [f.key] .Contains（f.someValue））
  

这会产生错误

  LINQ to Entities不识别方法' System.Collections.Generic.List`1 [System.Int32] get_Item（Int32）'方法
  

while列表它的作品：

 列表< int> l = new ... 
 var a = SomeEntity.Where（f => l.Contains（f.someValue））
  

这是linq对EF的限制，还是我缺少某些东西？

解决方案

实体框架的性质 - 当您将表达式放入查询时，它会尽力将其转换为SQL，以便可以在服务器上完成此工作。

您的第一个例子，它尝试将查询和字典检索调用转换为SQL，并且无法，因为它不知道如何。

在您的第二个例如，您只是将一个列表传递到查询中。它确实知道如何将它转换成SQL。

有一些这样的问题，所以你只需要注意它，然后再定义你的EF查询。 p>

编辑

刚刚注意到，您的第一个查询在阅读时会使用查询结果从字典。因此，由于您实际上无法将您的字典传递到SQL查询中，您可能需要首先从DB中检索所有记录，然后使用LINQ对象执行检查，如：

 字典< int，List< int>> dict = new ... 
 var a = SomeEntity 
 .ToArray（）
 .Where（f => dict [f.key] .Contains（f.someValue））; 
  

ToArray（）方法拉出整个结果设置为内存（有其他方法可以执行，但这是我通常做的），以及 Where 子句在LINQ对象中运行，而不是LINQ到实体，这意味着您的字典代码将运行正常。

I have:

Dictionary<int, List<int>> dict = new ...
var a = SomeEntity.Where(f => dict[f.key].Contains(f.someValue)) 

this produces error

 LINQ to Entities does not recognize the method 'System.Collections.Generic.List`1[System.Int32] get_Item(Int32)' method

while with lists it works:

List<int> l = new ...
var a = SomeEntity.Where(f => l.Contains(f.someValue))

So is this limitation of linq to EF or am I missing something?

解决方案

It's the nature of Entity Framework - when you put expressions into your query, it does its best to translate them into SQL, so that the work can be done on the server.

In your first example, it tries to translate both the query and the dictionary retrieval call into SQL, and is unable, since it doesn't know how to.

In your second example, you're just passing a list into the query. It does know how to translate that into SQL.

There are a few gotchas like that, so you just have to be mindful about it before defining your EF query.

EDIT

Just noticed that your first query makes use of the query results when reading from the dictionary. So since you can't actually pass your dictionary into the SQL query, you'll probably need to retrieve all the records from the DB first, then use LINQ-to-objects to perform your check, like:

Dictionary<int, List<int>> dict = new ...
var a = SomeEntity
    .ToArray()
    .Where(f => dict[f.key].Contains(f.someValue));

The ToArray() method pulls the entire result set into memory (there are other ways of doing it, but this is how I usually do it), and the following Where clause runs in LINQ-to-objects instead of LINQ-to-Entities, meaning your dictionary code will run fine.

这篇关于linq到实体框架：在查询中使用字典的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！