来自 SQL 的 LINQ-TO-Entities-解网

问：

我需要一些帮助将下面的 SQL 转换为 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;

到目前为止，我已经尝试像下面这样编写它，其中我使用模型导航属性而不是使用 GroupJoin/SelectMany，但下面的 LINQ 无法按行按外顺序翻译

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

错误消息：System.InvalidOperationException：无法转换 LINQ 表达式。以可翻译的形式重写查询，或者通过插入对“AsEnumerable”、“AsAsyncEnumerable”、“ToList”或“ToListAsync”的调用来显式切换到客户端评估。有关详细信息，请参阅 https://go.microsoft.com/fwlink/?linkid=2101038

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

我可以如上所示重写，但我不想使用客户端评估来避免内存问题

C# LINQ-to-entities EF-CORE-7.0

public async Task CastToListAsync()
{
    var data = (await _db.StudentLocation.AsNoTracking()
        .Join(_db.Student.AsNoTracking().Where(x => x.IsActive), 
            a => a.StudentID,
            b => b.ID,
            (a, b) => a
        )
        .Include(e => e.Location)
        .GroupBy(e => e.StudentID)
        .Select(x => x
            .OrderByDescending(y => y.Location.CityName)
            .ThenBy(z => z.StudentID)
            .FirstOrDefault()
        )
        .ToListAsync())
        .OrderByDescending(x => x.Location.CityName)
        .Skip(5000)
        .Take(500)
        .Select(x => x.StudentID);
}

关于可以使用 Enumerable/AsyncEnumerable 的内存问题，可以使用以下方法之一。

public void CastAsEnumerable()
{
    var data = _db.StudentLocation.AsNoTracking()
        .Join(_db.Student.AsNoTracking().Where(x => x.IsActive), 
            a => a.StudentID,
            b => b.ID,
            (a, b) => a
        )
       .Include(e => e.Location)
       .GroupBy(e => e.StudentID)
       .Select(x => x
            .OrderByDescending(y => y.Location.CityName)
            .ThenBy(z => z.StudentID)
            .FirstOrDefault()
       )
       .AsEnumerable()
       .OrderByDescending(x => x.Location.CityName)
       .Skip(5000)
       .Take(500)
       .Select(x => x.StudentID);
}

// AsyncEnumerable

public void CastToAsyncEnumerable()
{
    var data = BuildQuery().ToBlockingEnumerable()
        .OrderByDescending(x => x.Location.CityName)
        .Skip(5000)
        .Take(500)
        .Select(x => x.StudentID);
}

private async IAsyncEnumerable<StudentLocation?> BuildQuery()
{
    var query = _db.StudentLocation.AsNoTracking()
        .Join(_db.Student.AsNoTracking().Where(x => x.IsActive),
            a => a.StudentID,
            b => b.ID,
            (a, b) => a
        )
        .Include(e => e.Location)
        .GroupBy(e => e.StudentID)
        .Select(x => x
            .OrderByDescending(y => y.Location.CityName)
            .ThenBy(z => z.StudentID)
            .FirstOrDefault()
        )
        .AsAsyncEnumerable();

        await foreach (var data in query)
        {
            yield return data;
        }
}

（注意：我不完全确定这是否是使用 AsyncEnumerable 的最佳方式，因为我没有太多经验）

通过 BenchmarkDotNet 运行这些，将获得以下结果：

我不应该，这是假设对集合没有其他操作，它只是按原样返回。

如果开始对返回的数据执行操作，但 Enumerables 尚未具体化，则最终可能会导致更多性能问题。

使用 Enumerables，随着 N 个操作数的增加，分配的内存和执行时间将增加，而 List 操作则保持相当一致。

上一个：EF 上的歧义。使用 EF Core 7 的函数

下一个：EF 生成低效查询

来自 SQL 的 LINQ-TO-Entities

LINQ-TO-Entities from SQL

评论