请求将指定SQL转换为可服务器端执行的LINQ-to-Entities查询

原SQL语句 #

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;

尝试的LINQ代码及报错 #

尝试用模型导航属性编写LINQ，但外层的OrderByDescending无法被EF翻译，完整代码如下：

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

报错信息：

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'.

避免使用的客户端评估方案 #

以下方案通过先加载全量数据到内存再处理，会引发内存占用问题，因此不希望采用：

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

正确的LINQ-to-Entities实现 #

直接对应原SQL的窗口函数逻辑，使用EF Core的EF.Functions.RowNumber()方法生成等价SQL，确保全流程在数据库端执行：

方式一：使用Join关联表 #

var students = await context.StudentEntities
    .AsNoTracking()
    .Where(s => s.IsActive)
    .Join(context.StudentLocationEntities.AsNoTracking(),
        student => student.StudentId,
        location => location.StudentId,
        (student, location) => new { student.StudentId, location.LocationEntity })
    .Select(item => new 
    {
        item.StudentId,
        City = item.LocationEntity != null ? item.LocationEntity.CityName : "",
        RowNumber = EF.Functions.RowNumber()
            .Over(
                PartitionBy(item.StudentId)
                .OrderByDescending(item => item.LocationEntity != null ? item.LocationEntity.CityName : "")
            )
    })
    .Where(result => result.RowNumber == 1)
    .OrderByDescending(result => result.City)
    .Skip(5000)
    .Take(500)
    .Select(result => result.StudentId)
    .ToListAsync();

方式二：利用导航属性简化代码 #

如果StudentEntity定义了StudentLocations导航属性，可使用SelectMany替代Join，更符合EF的使用习惯：

var students = await context.StudentEntities
    .AsNoTracking()
    .Where(s => s.IsActive)
    .SelectMany(
        student => student.StudentLocations.DefaultIfEmpty(),
        (student, location) => new { student.StudentId, location.LocationEntity }
    )
    .Select(item => new 
    {
        item.StudentId,
        City = item.LocationEntity != null ? item.LocationEntity.CityName : "",
        RowNumber = EF.Functions.RowNumber()
            .Over(
                PartitionBy(item.StudentId)
                .OrderByDescending(item => item.LocationEntity != null ? item.LocationEntity.CityName : "")
            )
    })
    .Where(result => result.RowNumber == 1)
    .OrderByDescending(result => result.City)
    .Skip(5000)
    .Take(500)
    .Select(result => result.StudentId)
    .ToListAsync();

说明 #

原尝试代码中GroupBy后嵌套OrderByDescending取第一条的写法，EF难以将其翻译为对应的SQL窗口函数逻辑。而直接使用RowNumber().Over()可以精准匹配原SQL的分区排序逻辑，让EF生成等价的SQL语句，所有筛选、排序、分页操作都在数据库端完成，避免客户端评估带来的内存问题。

内容的提问来源于stack exchange，提问作者Kumar