LINQ-TO-Entities from SQL

Question

I need some help converting below SQL into LINQ

SELECT a.Id FROM
(
    SELECT 
        s.student_id AS Id, 
        COALESCE(l.city_name, '') AS City,
        ROW_NUMBER() OVER (
            PARTITION BY s.student_id 
            ORDER BY COALESCE(l.city_name, '') DESC) 
        AS RowNumber
    FROM student s
    JOIN student_location sl 
      ON s.student_id = sl.student_id 
    LEFT JOIN location l 
      ON l.location_id = sl.location_id 
    WHERE s.is_active
) a
WHERE a.RowNumber = 1
ORDER BY a.City DESC
LIMIT 500 
OFFSET 5000;

So far I have tried writing it like below where I am using the models navigation properties instead of using GroupJoin/SelectMany but the below LINQ is failing to be translated at the outer order by line

.OrderByDescending(x => x.Location.CityName)

var students = await context.Set<StudentLocationEntity>().AsNoTracking()
   .Join(context.Set<StudentEntity>().AsNoTracking().Where(x => x.IsActive),
       a => a.StudentId,
       b => b.StudentId,
       (a, b) => a)
   .Include(e => e.LocationEntity)
   .GroupBy(e => e.StudentId)
   .Select(x => x.OrderByDescending(y => y.Location.CityName).ThenBy(z => z.StudentId).FirstOrDefault())
   .OrderByDescending(x => x.Location.CityName)
   .Skip(5000)
   .Take(500)
   .Select(x => x.StudentId)
   .ToListAsync();

Error message : System.InvalidOperationException : The LINQ expression 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 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information

var data = await context.Set<StudentLocationEntity>().AsNoTracking()
   .Join(context.Set<StudentEntity>().AsNoTracking().Where(x => x.IsActive),
       a => a.StudentId,
       b => b.StudentId,
       (a, b) => a)
   .Include(e => e.LocationEntity)
   .GroupBy(e => e.StudentId)
   .Select(x => x.OrderByDescending(y => y.Location.CityName).ThenBy(z => z.StudentId).FirstOrDefault())
   .ToListAsync();

var students = data
   .OrderByDescending(x => x.Location.CityName)
   .Skip(5000)
   .Take(500)
   .Select(x => x.StudentId);

I can rewrite as shown above but I don't want to go with client evaluation to avoid memory issues