Entity Framework - "Unable to create a constant value of type 'Closure type'..." error

Question

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.

When I try to enumerate the following Linq query?

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?

Answer 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.

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.

Answer 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.

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.

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:

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.

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).

Now generate an edmx from it.

Now imagine you want to find all the people having a certain ID value, say 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...

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

Answer 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...

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!

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!