Entity Framework - "All" method

Question

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)

This 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;

Answer 1

The Entity Framework does not support all queries. This becomes obviouse 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 entites into local memory using ToList() and then use LINQ to Object.

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

It depends on you 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 regonize 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.