EF Core 7.0 (PostgresSQL) build IQueryable with RowNumber concept

Question

I have a scenario where one Student (identified by StudentId) has many Courses. Each Course has StudentId(FK) and a Grade (simple text type). I want to build an IQueryable to pull 10 StudentIds (skipping first 5) by Course first Grade when sorted in descending order.

Tried below query but getting System.InvalidOperationException : The LINQ expression cannot be translated

    var studentQuery= context.Set<Student>().AsNoTracking().Where(x=>x.IsActive);
    var courseQuery= context.Set<Course>().AsNoTracking()
        .OrderByDescending(x => x.Grade)
        .GroupBy(x => x.StudentId)
        .SelectMany(x => x.Select((b, i) => new { b, rn = i + 1 }))
        .Where(x => x.rn == 1).Select(x => x.b);
    return studentQuery.Join
    (
        courseQuery,
        x => x.StudentId,
        y => y.StudentId,
        (a, b) => b
    )
    .Skip(5)
    .Take(10)
    .Select(x => x.StudentId);

Answer 1

I was able to resolve the issue using the query below. Used GroupJoin (instead Join) to handle "students without any courses" use case. When a course has no Grade, used null coalescing operator to fix that. Such students will be last in the pagination. I was surprised that Max worked on text/string data type. Although this is working am sure there is a better way to fix this.

var studentQuery= context.Set<Student>().AsNoTracking().Where(x=>x.IsActive);   
var courseQuery= context.Set<Course>().AsNoTracking();

return studentQuery.GroupJoin
(
    courseQuery,
    x => x.StudentId,
    y => y.StudentId,
    (a, courses) => new { a.StudentId, courses }
)
.SelectMany(x => x.courses.DefaultIfEmpty(), (a, b) => new { a, b })
.GroupBy(x => x.a.StudentId)
.OrderByDescending(x => x.Max(y => y.b.Grade ?? string.Empty))
.Skip(5)
.Take(10)
.Select(x => x.Key);

Generated postgres SQL looks like

SELECT r.student_id
FROM student AS r
LEFT JOIN (
    SELECT r0.grade, r0.student_id
    FROM course AS r0
) AS t ON r.student_id = t.student_id
WHERE r.is_active
GROUP BY r.student_id
ORDER BY max(COALESCE(t.grade,'')) DESC
LIMIT 10 OFFSET 5