实体框架 - “所有”方法 [英] Entity Framework - "All" method

问题描述

所有方法应该针对列表中的所有元素来评估参数。它可以在常规Linq中正常工作，但是当我尝试使用EF时，它会抛出一个错误（无法创建Closure类型的常量值，只有原始类型（例如Int32，String和Guid）

示例：

  var myList = from entities in entities.People 
 where searchList.All（arg => arg == arg）.ToList（）; 
  

（arg == arg这里只是为了说明我的问题） / p>

在我的场景中，searchList是一个包含搜索项的列表，如约翰，会计，75。在我的EF查询中，我想检索人物中的所有记录，John，Accounting和75出现在某些指定的可搜索字段中。一个更现实的例子是这样的：

 其中SearchList.All（（person.FirstName ++ person.LastName ++ person.DepartmentName ++ person.Phone）.Contains）
  

第二个例子也可以和Linq一起工作，在记忆中，但EF不喜欢。

请帮忙！我可以做些什么来使其工作？

这是一个更具体的问题，源于另一个问题。

示例代码：

  IEnumerable< string> searchList = ParseSearchText（searchText）; //搜索文本被分解成搜索令牌 - 每个标记都是searchList中的一个元素。例如John，Sales，654
 
 var peopleQuery = from persons in entities.vSearchPeople 
 where upperSearchList.All（（person.FirstName ++ person.Lastname ++ person.Phone）.ToUpperInvariant（）。包含）
选择人; 
  

解决方案

实体框架不支持所有查询。如果你想到以下这样的东西，这很明显：

  dataContext.Persons.Where（person => MyMethod（person）） ; 
  

与 MyMethod（）返回一个布尔值。该方法可能会执行所有操作，您无法将所有内容翻译成SQL。解决方案是使用 ToList（）将所有实体变成本地内存，然后使用LINQ to Object。

  dataContext.Persons.ToList（）。Where（person => MyMethod（person））; 
  

这取决于您的实际查询是否可以重写，以便可以将其转换为SQL实体框架，或者您必须使用LINQ to Object在本地内存中执行查询。

您提到的异常类似于您正在尝试以下操作。 p>

 公司公司= datacontext.Companies.Where（company.Name ==ACME）。 
 
 dataContext.Employees.Where（employee => employee.Company == company）; 
  

LINQ to Entity不支持包含实体的表达式，因此比较公司实体无效。在这种情况下，您可以重写如下。

  dataContext.Employees.Where（employee => employee.Company.Id = = company.Id）; 
  

这只比较ids - 像整数或GUID这样的原始类型 - 这可以被转换进入SQL。

逐个搜索的示例（另见评论）

 的IQueryable<人与GT;结果=实体。 
 
 foreach（searchList中的字符串项）
 {
 //这个副本重要的是为了不修改闭包。 
 String itemCopy = item; 
 
 result = result.Where（p => 
 p.FirstName.ToUpper（）。包含（itemCopy）|| 
 p.LastName.ToUpper（） itemCopy）|| 
 p.Phone.ToUpper（）。包含（itemCopy））; 
} 
  

这将逐个构建查询。注意到实体框架承认 ToUpper（）， ToLower（）和 Contains（） （还有一些） - 所以当我说实体框架不能识别方法调用时，我是严格的。它不是很多，而是不是 ToUpperInvariant（）和 ToLowerInvariant（）。此外，该查询使用列的排序方式转换为 CHARINDEX（）函数调用，因此，搜索可以不区分大小写，而不显式 ToUpper（）或 ToLower（）呼叫。

The All method is supposed to evaluate the argument against all elements in the list. It works OK in regular Linq but when I try to use it with EF it throws an error ("Unable to create a constant value of type 'Closure type'. Only primitive types (for instance Int32, String and Guid) are supported in this context. ")

Example:

var myList = from person in entities.People
             where searchList.All(arg => arg == arg).ToList();

(arg == arg here is just to illustrate my question)

In my scenario, searchList is a List containing search items, such as "John", "Accounting", "75". In my EF query I want to retrieve all records in People which John, Accounting and 75 appear in some specified searchable fields. A more realistic example would be something like this:

where SearchList.All((person.FirstName + " " + person.LastName + " " + person.DepartmentName + " " + person.Phone).Contains)

The second example also works OK with Linq, in memory, but EF doesn't like it.

Please help! What can I do to make it work?

This is a more specific question derived from another question of mine.

Sample code:

IEnumerable<string> searchList = ParseSearchText(searchText); //search text is broken into search tokens - each token is an element in searchList. For instance "John", "Sales", "654"

var peopleQuery = from person in entities.vSearchPeople
where upperSearchList.All((person.FirstName + " " + person.Lastname + " " + person.Phone).ToUpperInvariant().Contains)
select person;

解决方案

The Entity Framework does not support all queries. This becomes obvious if you think of something like the following

dataContext.Persons.Where(person => MyMethod(person));

with MyMethod() returning a Boolean value. The method might do everything and you cannot translate everything into SQL. The solution is to get all entities into local memory using ToList() and then use LINQ to Object.

dataContext.Persons.ToList().Where(person => MyMethod(person));

It depends on your actual query if it can be rewritten, so that it can be transformed into SQL by the Entity Framework or if you have to do the query in local memory using LINQ to Object.

The exception you mentioned sound like you are trying something like the following.

Company company = datacontext.Companies.Where(company.Name == "ACME").Single();

dataContext.Employees.Where(employee => employee.Company == company);

LINQ to Entity does not support expressions containing entities, hence the comparison of the Company entities is not valid. In this case you can rewrite it as follows.

dataContext.Employees.Where(employee => employee.Company.Id == company.Id);

This compares only the ids - a primitive type like a integer or a GUID - and this can be transformed into SQL.

Example for search word by word (see also the comments)

IQueryable<People> result = entities.People;

foreach (String item in searchList)
{
    // This copy is important in order not to modify the closure.
    String itemCopy = item;

    result = result.Where(p =>
        p.FirstName.ToUpper().Contains(itemCopy) ||
        p.LastName.ToUpper().Contains(itemCopy) ||
        p.Phone.ToUpper().Contains(itemCopy));
}

This will construct the query word by word. Noted that the Entity Framework recognizes ToUpper(), ToLower(), and Contains() (and some more) - so I was to strict when I said that the Entity Framework does not recognize method calls. It does, but not many and not ToUpperInvariant() and ToLowerInvariant(). Further this query translates into CHARINDEX() function calls using the collation of the column, hence the search can be case insensitive without explicit ToUpper() or ToLower() calls.

这篇关于实体框架 - “所有”方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！