[ACCEPTED]-Entity Framework - "All" method-linq-to-entities

Accepted answer
Score: 14

The Entity Framework does not support all 28 queries. This becomes obvious if you think 27 of something like the following

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

with MyMethod() returning 26 a Boolean value. The method might do everything 25 and you cannot translate everything into 24 SQL. The solution is to get all entities 23 into local memory using ToList() and then use LINQ 22 to Object.

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

It depends on your actual query 21 if it can be rewritten, so that it can be 20 transformed into SQL by the Entity Framework 19 or if you have to do the query in local 18 memory using LINQ to Object.

The exception 17 you mentioned sound like you are trying 16 something like the following.

Company company = datacontext.Companies.Where(company.Name == "ACME").Single();

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

LINQ to Entity 15 does not support expressions containing 14 entities, hence the comparison of the Company entities 13 is not valid. In this case you can rewrite 12 it as follows.

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

This compares only the ids 11 - a primitive type like a integer or a GUID 10 - 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) ||

This 9 will construct the query word by word. Noted 8 that the Entity Framework recognizes ToUpper(), ToLower(), and 7 Contains() (and some more) - so I was to strict when 6 I said that the Entity Framework does not 5 recognize method calls. It does, but not 4 many and not ToUpperInvariant() and ToLowerInvariant(). Further this query translates 3 into CHARINDEX() function calls using the collation 2 of the column, hence the search can be case 1 insensitive without explicit ToUpper() or ToLower() calls.

Score: 0

This good example what good workin 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();


More Related questions