"Unable to generate a constant variable of type 'Closure type'..." problem in Entity Framework

entity-framework linq

Question

What causes 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 attempting the following Linq query, what happens?

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();
}

If I do the following only to try to pinpoint the issue, I still get the same error: Update

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

So it seems that the All technique is the cause of the issue, right? Any recommendations?

1
79
7/8/2015 12:31:36 PM

Accepted Answer

You seem to be attempting to implement a "WHERE...IN" condition equivalent. See How to use LINQ to Entities to create where-in style queries for an illustration of how to use LINQ to Entities for that kind of query.

Also, I think the error message is particularly unhelpful in this case because.Contains without parenthesis, the compiler interprets the whole predicate as a lambda expression.

69
7/8/2015 12:36:31 PM

Popular Answer

While I'm not the world's brightest guy, I've spent the past six months working with EF 3.5 to overcome this issue, so I believe I have something to contribute to the discussion.

A bad query execution plan will be produced by the SQL produced by constructing a "OR style" expression tree that is 50 miles high. With a few million rows to work with, the effect is significant.

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 the name of the Entity1 table's primary key id column.

BUT CONTINUE READING!

That's great, however it requires that I already know the ids of the things I'm looking for. Sometimes all I want is for my expressions to touch other associated ties, and for that I do have standards.

I would attempt to illustrate this graphically if I had more time, but I don't, so take a minute to consider the following sentence: Think of a schema that has the tables Person, GovernmentId, and GovernmentIdType. Two government identification cards—one from Oregon (GovernmentIdType) and one from Washington—are held by Andrew Tappert (Person) (GovernmentIdType).

Create an edmx from it now.

Imagine that you wish to locate everyone who has an ID value of, say, 1234567.

This can be done with only one database hit by doing 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 recognize this subquery? Sub-queries are not used in the produced sql; instead, joins are used, but the result is the same. Nowadays, SQL Server automatically optimizes subqueries into joins, but yet...

The.Any within the statement is essential to this functioning.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow