Entity Framework - All Method

Question

The Everything method must evaluate the argument against all elements in the list. It works fine in regular Linq, but when I try to use it with EF, it throws an error ("It is not possible to create a constant value of the" Closure type "type. In this case, only primitive types (for example, Int32, String and Guid) are supported." )

Example:

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

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

In my script, searchList is a list containing search items such as "John", "Accounting", "75". In my EF query, I want to get all the entries in People that John, Accounting, and 75 appear in some specific search fields. A more realistic example might be something like this:

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

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

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

This is a more specific question derived from another question .

Code example:

 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

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

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

with MyMethod() returning a boolean value. A method can do everything, and you cannot translate everything into SQL. The solution is to get all the objects in 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 converted to SQL using the Entity Framework or if you need to execute the query in local memory using LINQ to Object.

The exception you were talking about looks like you are trying to do 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 objects, so comparing Company objects is not valid. In this case, you can rewrite it as follows.

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

It compares only identifiers - a primitive type such as an integer or GUID, and it can be converted to SQL.

Example search by word (see also 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 build the query word by word. It has been noted that the Entity Framework recognizes ToUpper() , ToLower() and Contains() (and a few more) - so I was strict when I said that the Entity Framework does not recognize method calls. This happens, but not much, not ToUpperInvariant() and ToLowerInvariant() . Further, this query is translated into calls to the CHARINDEX() functions using column sorting, so the search can be case insensitive without explicit calls to ToUpper() or ToLower() .

Answer 2

This is a good example of what works well with the All () method.

 model.ReferenceList = db.JournalCardReference.OrderBy(a => a.orderF) .Include(x => x.JournalCardField) .Where(x => x.JournalCardField .All(f => f.deleted == null || f.deleted != true)).ToList();