实体框架 - “无法创建类型'闭包类型'的常量值...”错误

Why do I get the 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.

无法创建“闭包类型”类型的常量值。在此上下文中仅支持基本类型（例如Int32，String和Guid）。

When I try to enumerate the following Linq query?

当我尝试枚举以下Linq查询？

IEnumerable<string> searchList = GetSearchList();
using (HREntities entities = new HREntities())
{
   var myList = from person in entities.vSearchPeople
   where upperSearchList.All( (person.FirstName + person.LastName) .Contains).ToList();
}

Update: If I try the following just to try to isolate the problem, I get the same error:

更新：如果我尝试以下尝试隔离问题，我得到相同的错误：

where upperSearchList.All(arg => arg == arg)

So it looks like the problem is with the All method, right? Any suggestions?

所以看起来问题出在All方法上，对吧？有什么建议么？

4 个解决方案

#1

It looks like you're trying to do the equivalent of a "WHERE...IN" condition. Check out How to write 'WHERE IN' style queries using LINQ to Entities for an example of how to do that type of query with LINQ to Entities.

看起来你正试图做相当于“WHERE ... IN”的条件。查看如何使用LINQ to Entities编写'WHERE IN'样式查询，以获取如何使用LINQ to Entities进行该类型查询的示例。

Also, I think the error message is particularly unhelpful in this case because .Contains is not followed by parentheses, which causes the compiler to recognize the whole predicate as a lambda expression.

此外，我认为错误消息在这种情况下特别无用，因为.Contains后面没有括号，这会导致编译器将整个谓词识别为lambda表达式。

#2

I've spent the last 6 months battling this limitation with EF 3.5 and while I'm not the smartest person in the world, I'm pretty sure I have something useful to offer on this topic.

我花了最近6个月与EF 3.5争夺这个限制，虽然我不是世界上最聪明的人，但我很确定我在这个主题上有一些有用的东西。

The SQL generated by growing a 50 mile high tree of "OR style" expressions will result in a poor query execution plan. I'm dealing with a few million rows and the impact is substantial.

通过增加50英里高的“OR style”表达式树生成的SQL将导致查询执行计划不佳。我正在处理几百万行，影响很大。

There is a little hack I found to do a SQL 'in' that helps if you are just looking for a bunch of entities by id:

我发现做一个SQL'in'有一点hack，如果你只是通过id寻找一堆实体，这会有所帮助：

private IEnumerable<Entity1> getByIds(IEnumerable<int> ids)
{
    string idList = string.Join(",", ids.ToList().ConvertAll<string>(id => id.ToString()).ToArray());
    return dbContext.Entity1.Where("it.pkIDColumn IN {" + idList + "}");
}

where pkIDColumn is your primary key id column name of your Entity1 table.

其中pkIDColumn是Entity1表的主键id列名。

BUT KEEP READING!

但继续阅读！

This is fine, but it requires that I already have the ids of what I need to find. Sometimes I just want my expressions to reach into other relations and what I do have is criteria for those connected relations.

这很好，但它要求我已经拥有了我需要找到的东西。有时我只是希望我的表达能够与其他关系联系起来，而我所拥有的是那些相关关系的标准。

If I had more time I would try to represent this visually, but I don't so just study this sentence a moment: Consider a schema with a Person, GovernmentId, and GovernmentIdType tables. Andrew Tappert (Person) has two id cards (GovernmentId), one from Oregon (GovernmentIdType) and one from Washington (GovernmentIdType).

如果我有更多的时间，我会尝试直观地表示这一点，但我不是那么只研究这句话：考虑一个带有Person，GovernmentId和GovernmentIdType表的模式。 Andrew Tappert（Person）有两张身份证（GovernmentId），一张来自Oregon（GovernmentIdType），一张来自华盛顿（GovernmentIdType）。

Now generate an edmx from it.

现在从中生成一个edmx。

Now imagine you want to find all the people having a certain ID value, say 1234567.

现在想象你想要找到所有具有特定ID值的人，比如1234567。

This can be accomplished with a single database hit with this:

这可以使用以下单个数据库命中来完成：

dbContext context = new dbContext();
string idValue = "1234567";
Expression<Func<Person,bool>> expr =
    person => person.GovernmentID.Any(gid => gid.gi_value.Contains(idValue));

IEnumerable<Person> people = context.Person.AsQueryable().Where(expr);

Do you see the subquery here? The generated sql will use 'joins' instead of sub-queries, but the effect is the same. These days SQL server optimizes subqueries into joins under the covers anyway, but anyway...

你在这里看到子查询吗？生成的sql将使用'join'而不是子查询，但效果是相同的。这些天SQL服务器无论如何都将子查询优化为连接下的连接，但无论如何......

The key to this working is the .Any inside the expression.

这项工作的关键是表达式中的.Any。

#3

I have found the cause of the error (I am using Framework 4.5). The problem is, that EF a complex type, that is passed in the "Contains"-parameter, can not translate into an SQL query. EF can use in a SQL query only simple types such as int, string...

我找到了错误的原因（我正在使用Framework 4.5）。问题是，EF是一个复杂类型，它在“包含”参数中传递，不能转换为SQL查询。 EF只能在SQL查询中使用简单类型，如int，string ......

this.GetAll().Where(p => !assignedFunctions.Contains(p))

GetAll provides a list of objects with a complex type (for example: "Function"). So therefore, I would try here to receive an instance of this complex type in my SQL query, which naturally can not work!

GetAll提供具有复杂类型的对象列表（例如：“Function”）。所以，我会在这里尝试在我的SQL查询中接收这个复杂类型的实例，这自然是行不通的！

If I can extract from my list, parameters which are suited to my search, I can use:

如果我可以从我的列表中提取适合我搜索的参数，我可以使用：

var idList = assignedFunctions.Select(f => f.FunctionId);
this.GetAll().Where(p => !idList.Contains(p.FunktionId))

Now EF no longer has the complex type "Function" to work, but eg with a simple type (long). And that works fine!

现在EF不再具有复杂类型“功能”，但是例如使用简单类型（长）。这很好！

#4

I got this error message when my array object used in the .All function is null After I initialized the array object, (upperSearchList in your case), the error is gone The error message was misleading in this case

当我在.All函数中使用的数组对象为null时，我收到此错误消息在初始化数组对象后，（在您的情况下为upperSearchList），错误消失了错误消息在这种情况下有误导性

where upperSearchList.All(arg => person.someproperty.StartsWith(arg)))

whereSearchSearchList.All（arg => person.someproperty.StartsWith（arg）））

#1