Finding common items is evaluated locally

Question

Using Entity Framework Core 2.2 I have the following query:

IQueryable<User> users = _context.Users.AsNoTracking();

User user = await users
  .Include(x => x.UserSkills)
  .ThenInclude(x => x.Skill)
  .FirstOrDefaultAsync(x => x.Id == 1);

var userSkills = user.UserSkills.ToList();

IQueryable<Lesson> lessons = _context.Lessons.AsNoTracking();

var test = lessons
  .Where(x => x.IsEnabled)
  .Where(x => x.LessonSkills.All(y => userSkills.Any(z => y.SkillId == z.SkillId))) 
  .ToList();

I am looking to get User Skills contains all Lesson Skills.

When I run this query I get the following error:

Exception thrown: 'System.InvalidOperationException' in System.Private.CoreLib.dll: 
'Error generated for warning 'Microsoft.EntityFrameworkCore.Query.QueryClientEvaluationWarning: 
The LINQ expression 'where ([y].SkillId == [z].SkillId)' could not be translated and will be evaluated locally.'.

How to change the query to solve this problem?

Update

I need to extend this query with an extra option (y.SkillLevelId <= z.SkillLevelId):

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

Answer 1

userSkills is in-memory collection, and from my experience with EF6 and EF Core so far I can say that the only reliable translatable construct with in-memory collections is Enumerable.Contains method on primitive type in-memory collection.

So the following solves the problem is question.

First (should be outside the query expression tree):

var userSkillIds = user.UserSkills.Select(x => x.SkillId);

Then instead of

.Where(x => x.LessonSkills.All(y => userSkills.Any(z => y.SkillId == z.SkillId)))

use the equivalent (but translatable):

.Where(x => x.LessonSkills.All(y => userSkillIds.Contains(y.SkillId)))

Update: If you can't use Contains, the options you have until EF Core starts supporting it are (1) EntityFrameworkCore.MemoryJoin package (I personally haven't tested it, but the idea is interesting), (2) manually building Or based predicate with Expression class (hard and works for small memory collections) and (3) replace the memory collection with real IQueryable<>, for instance

var userSkills = users
    .Where(x => x.Id == 1)
    .SelectMany(x => x.UserSkills);

and use the original query.