Evaluate EF query with MAX on the server

Question

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?

You could get the max value as a separate query (i.e. `var maxLessonLevelId = user.Lessons.Max(y => y.LessonLevelId);`) — DavidG, May 10 '19 at 12:20
I am not sure I understood your answer ... I want to get all lessons which LessonLevelId is less than the Maximum LessonLevelId of all user Lessons. — Miguel Moura, May 10 '19 at 12:33
Yes, you would then use `maxLessonLevelId` instead of `user.Lessons.Max(y => y.LessonLevelId)` in your second query. — DavidG, May 10 '19 at 12:35
Currently you have 2 queries. Do you really need `user` instance (for something else) or it is just for the second query? — Ivan Stoev, May 10 '19 at 12:54
@IvanStoev I need User because I am filtering lessons with other user properties not just using user lessons. For exemple, o use User country to make additional filters. — Miguel Moura, May 10 '19 at 13:24
@DavidG I just added an update with your suggestion and a question. Is it possible to use a child query that evaluates on the server? Just wondering ... — Miguel Moura, May 10 '19 at 13:48
@MiguelMoura It's possible, but that's a different question. And the answer is here https://stackoverflow.com/questions/55815535/how-to-translate-this-sql-query-to-a-linq-query-in-ef-core/55818315#comment98324477_55818315. Shortly, you should use nullable overload of `Max`. — Ivan Stoev, May 10 '19 at 15:32
@IvanStoev Thank you! That solved it without the need for defining maxLessonLevelId. I will check which one is the better option. — Miguel Moura, May 10 '19 at 15:44

score 2 · Accepted Answer · edited May 11 '19 at 04:44

2

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);

edited May 11 '19 at 04:44

TanvirArjel

30,049
14
78
114

answered May 10 '19 at 13:26

DavidG

113,891
12
217
223

A side question: is there any way to use a child query that evaluates on the server as I did on my update? I know the one I posted does not. Just curious ... – Miguel Moura May 10 '19 at 13:53

Evaluate EF query with MAX on the server

1 Answers1