EF Core 3.x - simple LINQ with Include can not be translated, client evaluation

Question

I have a simple LINQ expression:

public List<Project> GetCheckedProjects(List<PickedUp> displayCollection)
        {
            IQueryable<Project> query = _context.Projects
                .OrderBy(project => project.CompletionDate)
                .Where(project => displayCollection.Any(item => item.ProjectID == project.ProjectID))
                .Include(project => project.TechnologiesProjects)
                .ThenInclude(techproj => techproj.Technology);
            return query.ToList();
        }

that drops me an runtime exception:

'The LINQ expression 'DbSet .OrderBy(p => p.CompletionDate) .Where(p => __displayCollection_0 .Any(item => item.ProjectID == p.ProjectID))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.'

In this MSDN article it is mentioned, that in EF Core 3.x were breaking changes, and they prevent client evaluation. And

In such cases, you can explicitly opt into client evaluation by calling methods like AsEnumerable or ToList (AsAsyncEnumerable or ToListAsync for async). By using AsEnumerable you would be streaming the results, but using ToList would cause buffering by creating a list, which also takes additional memory.

In my case, when using AsEnumerable() I am geting an exception:

IEnumerable does not contain a definition for 'Include' (...)

Also simple using ToList() at very end of expression, like in this SO answer, does not bring any positive result.

In EF Core 2.x my LINQ works fine. How to walk around this problem?

score 3 · Accepted Answer · answered Jul 05 '20 at 17:55

Try selecting the ids into separate collection and using Contains:

    public List<Project> GetCheckedProjects(List<PickedUp> displayCollection)
    {
        var ids = displayCollection.Select(item => item.ProjectID).ToList();
        IQueryable<Project> query = _context.Projects
            .OrderBy(project => project.CompletionDate)
            .Where(project => ids.Contains(project.ProjectID))
            .Include(project => project.TechnologiesProjects)
            .ThenInclude(techproj => techproj.Technology);
        return query.ToList();
    }

Or, since this code will be translated to query you can do it in one line:

IQueryable<Project> query = _context.Projects
            .OrderBy(project => project.CompletionDate)
            .Where(project => displayCollection.Select(item => item.ProjectID).Contains(project.ProjectID))
            .Include(project => project.TechnologiesProjects)
            .ThenInclude(techproj => techproj.Technology);

score 0 · Answer 2 · answered Jul 05 '20 at 17:23

Ef Core might be having trouble with the line:

displayCollection.Any(item => item.ProjectID == project.ProjectID)

Try doing it this way

public List<Project> GetCheckedProjects(List<PickedUp> displayCollection)
    {
        IQueryable<Project> queryAll = _context.Projects
            .OrderBy(project => project.CompletionDate)                
            .Include(project => project.TechnologiesProjects)
            .ThenInclude(techproj => techproj.Technology);
        
        var query = queryAll
              .ToList()
              .Where(project => displayCollection.Any(item => item.ProjectID == project.ProjectID));       
    
        return query;
    }

I think this should work because the EF core will not be translating the Where clause into a sql query. And reqular LINQ should be able to do it on a List.

The downside (if it works) is that you are going to loop through the entire database table when you call ".ToList()"

Actually it gives me the same exception like before. – bakunet Jul 05 '20 at 17:41 — bakunet, Jul 05 '20 at 17:41

EF Core 3.x - simple LINQ with Include can not be translated, client evaluation

2 Answers2