Query with All and Any evaluates locally

[mdmoura]

Using Entity Framework Core 2.2 I am trying to execute the following query on the database:

var user = context.Users.AsNoTracking()
  .Include(x => x.UserSkills).ThenInclude(x => x.Skill)
  .Include(x => x.UserSkills).ThenInclude(x => x.SkillLevel)
  .FirstOrDefault(x => x.Id == userId);

Then I tried the following query:

var lessons = _context.Lessons.AsNoTracking()
  .Where(x => x.LessonSkills.All(y => 
     user.UserSkills.Any(z => y.SkillId == z.SkillId && y.SkillLevelId <= z.SkillLevelId)))
  .ToList();

Exception

This lessons query evaluates in memory and I get the following exception:

The LINQ expression 'where (([y].SkillId == [z].SkillId) AndAlso ([y].SkillLevelId <= [z].SkillLevelId))' could not be translated and will be evaluated locally.'.

I tried a few options like creating a userSkills variable:

var userSkills = user.UserSkills.Select(z => new { 
  SkillId = z.SkillId, 
  SkillLevelId = z.SkillLevelId 
});

And then use userSkills in lessons query instead of user.UserSkills.

But still evaluates locally ... Is this a bug?

Further technical details

EF Core version: 2.2
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: macOS Mojave
IDE: Visual Studio Code 1.33.1

[sdanyliv]

It do not looks like a bug but misunderstanding how IQueryable works.
Rewrite in the following way (source for method chain should be IQueryable but not just list or collection):

var userSkills = _context.UserSkills
   .Where(us => us.UserId == userId)
   .Select(z => new { 
       SkillId = z.SkillId, 
       SkillLevelId = z.SkillLevelId 
    });

var lessons = _context.Lessons.AsNoTracking()
  .Where(x => x.LessonSkills.All(y => 
     userSkills.Any(z => y.SkillId == z.SkillId && y.SkillLevelId <= z.SkillLevelId)))
  .ToList();

[smitpatel]

Exception in 3.1

Unhandled exception. System.InvalidOperationException: The LINQ expression 'DbSet<LessonSkill>
    .Where(l0 => EF.Property<Nullable<int>>((EntityShaperExpression:
        EntityType: Lesson
        ValueBufferExpression:
            (ProjectionBindingExpression: EmptyProjectionMember)
        IsNullable: False
    ), "Id") != null && EF.Property<Nullable<int>>((EntityShaperExpression:
        EntityType: Lesson
        ValueBufferExpression:
            (ProjectionBindingExpression: EmptyProjectionMember)
        IsNullable: False
    ), "Id") == EF.Property<Nullable<int>>(l0, "LessonId"))
    .All(l0 => __user_UserSkills_0
        .Any(z => l0.SkillId == z.SkillId && l0.SkillLevelId <= z.SkillLevelId))' 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.

[smitpatel]

Duplicate of #19070