EF Core查询优化：如何避免低效Join，逼近手写Partition By SQL性能？

问题根源 #

你当前使用的GroupBy+First的Linq写法，EF Core在翻译时会生成冗余的分组子查询和关联操作，导致百万级数据下性能大幅下降。而手写SQL的核心逻辑是先过滤CanvasId，再通过开窗函数ROW_NUMBER()按坐标分组取最新记录，最后关联Canvas表，这种逻辑更高效。

优化后的Linq写法 #

要让EF Core生成接近手写版本的高效SQL，直接模拟手写SQL的开窗逻辑即可，无需使用GroupBy：

写法一：分步查询（更简洁）

// 先获取Canvas信息
var canvas = await _context.Canvas.FirstOrDefaultAsync(c => c.Id == canvasId);

// 获取每个坐标的最新像素记录
var latestPixels = await _context.Pixels
    .Where(p => p.CanvasId == canvasId)
    .Select(p => new 
    {
        p.Id,
        p.AuthorId,
        p.CanvasId,
        p.Color,
        p.CreatedAt,
        p.X,
        p.Y,
        // 按坐标分组，按创建时间倒序生成行号
        RowNumber = EF.Functions.RowNumber().Over(
            partitionBy: new { p.X, p.Y }, 
            orderBy: p => p.CreatedAt descending)
    })
    .Where(r => r.RowNumber == 1)
    .ToListAsync();

// 组合结果
var result = new 
{
    Canvas = canvas,
    Pixels = latestPixels
};

写法二：单次关联查询（统一查询逻辑）

如果希望通过一次数据库查询完成，可使用Join关联Canvas和处理后的Pixels：

var result = await _context.Canvas
    .Where(c => c.Id == canvasId)
    .Join(
        // 先处理Pixels：过滤、开窗、取最新记录
        _context.Pixels
            .Where(p => p.CanvasId == canvasId)
            .Select(p => new 
            {
                p.CanvasId,
                p.Id,
                p.AuthorId,
                p.Color,
                p.CreatedAt,
                p.X,
                p.Y,
                RowNumber = EF.Functions.RowNumber().Over(
                    partitionBy: new { p.X, p.Y }, 
                    orderBy: p => p.CreatedAt descending)
            })
            .Where(r => r.RowNumber == 1),
        // 关联条件
        canvas => canvas.Id,
        pixel => pixel.CanvasId,
        // 组合结果
        (canvas, pixel) => new { Canvas = canvas, Pixel = pixel }
    )
    // 按Canvas分组，整理成目标结构
    .GroupBy(item => item.Canvas)
    .Select(group => new 
    {
        Canvas = group.Key,
        Pixels = group.Select(item => item.Pixel)
    })
    .FirstOrDefaultAsync();

效果说明 #

优化后的Linq写法会生成与你手写SQL几乎一致的查询语句：

1. 先对pixels表过滤canvasId
2. 通过ROW_NUMBER()按x,y分组并按createdAt倒序排序
3. 筛选行号为1的记录（即每个坐标的最新像素）
4. 最后与canvas表关联

这种SQL在百万级数据下的性能会和你手写的版本接近，避免了冗余的分组和关联操作。

备选方案：直接执行手写SQL #

如果需要完全复用手写SQL的逻辑，也可以使用EF Core的FromSqlRaw方法保持类型安全：

var sql = @"
select c.id, p.x, p.y, p.color, p.id as PixelId, p.""authorId"", p.""createdAt"" 
from dbo.canvas c
inner join (
    select row_number() over (partition by p.x, p.y order by p.""createdAt"" desc) n, *
    from dbo.pixels p
    where p.""canvasId"" = @canvasId
) p on n = 1
where c.id = @canvasId;
";

var result = await _context.Set<CanvasPixelDto>()
    .FromSqlRaw(sql, new SqlParameter("@canvasId", canvasId))
    .ToListAsync();

// 需提前定义CanvasPixelDto类映射查询结果

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