CODEWITHSUNDEEP
entity-framework-corelinq-to-entitiesentity-framework-core-2.2
Miguel Moura
Miguel Moura

Reputation: 39484

Evaluate EF query with MAX on the server

Using Entity Framework Core 2.2 I have the following query:

var user = await context.Users.AsNoTracking()
  .Include(x => x.Lessons).ThenInclude(x => x.LessonLevel)
  .FirstOrDefaultAsync(x => x.Id == userId);

var lessons = context.Lessons.AsNoTracking();
  .Where(x => x.LessonLevelId < user.Lessons.Max(y => y.LessonLevelId));

Thus query evaluates locally and I get the message:

The LINQ expression 'Max()' could not be translated and will be evaluated locally.'

How can I make this query evaluate on the server?

Update

Based on DavigG answer I made it work using:

var maxLessonLevelId = user.Lessons.Max(y => y.LessonLevelId););

var lessons = context.Lessons.AsNoTracking();
  .Where(x => x.LessonLevelId < maxLessonLevelId);

I know the following evaluates locally but shouldn't evaluate on the server?

var lessons = context.Lessons.AsNoTracking();
  .Where(x => x.LessonLevelId < 
    context.Users.AsNoTracking()
      .Where(y => y.Id == userId)
      .Select(y => y.Lessons.Max(z => z.LessonLevelId))
      .FirstOrDefault());

Is it possible to use a child queries that evaluates on the server?

Upvotes: 1

Views: 321

Answers (1)

DavidG
DavidG

Reputation: 119156

Get the max value as a separate query, for example:

var maxLessonLevelId = user.Lessons.Max(y => y.LessonLevelId);

Then you can can get the lessons like this:

var lessons = context.Lessons.AsNoTracking()
                     .Where(x => x.LessonLevelId < maxLessonLevelId);

Upvotes: 1

Related Questions