EF Core：如何从数据库获取行号？解决大数据量查询问题

现有EF Core查询如下，返回超过50万条记录：

var productsQuery = context.Goods
    .Select((p) =>
        new {
            Name = p.Name,
            Type = p.Type,
            BestBefore = p.BestBefore,
            ShopId = p.ShopId
        })
    .Where((p) => p.Type == "Food");

需求是根据提前定义的selectedShopId，获取上述查询结果中对应产品的**行号（索引）**和产品对象。尝试了以下代码，但AsEnumerable()会把所有数据加载到内存处理，性能极差，无法解决问题：

// variable selectedShopId is given earlier
var productObject = productQuery
    .AsEnumerable()
    .Select((p, i) =>
        new {
            Index = i,
            Product = p
        })
    .Where(p => p.Product.ShopId == selectedShopId)
    .FirstOrDefault();

请问如何在EF Core中从数据库层面获取行号来实现需求？

直接在数据库层面使用窗口函数ROW_NUMBER()计算行号，EF Core 3.0及以上版本支持将窗口函数翻译为SQL，无需把大量数据加载到内存。

实现代码 #

// 提前定义的目标店铺ID
var selectedShopId = /* 你的店铺ID值 */;

var result = context.Goods
    // 先过滤食品类型，减少后续计算范围
    .Where(p => p.Type == "Food")
    // 计算行号并投影产品信息
    .Select(p => new {
        Product = new {
            p.Name,
            p.Type,
            p.BestBefore,
            p.ShopId
        },
        // 必须指定排序规则，确保行号稳定
        RowNumber = EF.Functions.RowNumber().Over(orderBy: o => o.OrderBy(p => p.Id))
    })
    // 过滤目标店铺的记录
    .Where(x => x.Product.ShopId == selectedShopId)
    // 转换为需要的结果格式（若需0起始索引则RowNumber减1）
    .Select(x => new {
        Index = x.RowNumber - 1,
        x.Product
    })
    .FirstOrDefault();

核心要点 #

• 排序规则不可省略：ROW_NUMBER()依赖明确的排序逻辑，否则数据库返回的行号会随机变化。建议选择业务上稳定的排序字段，比如主键Id。
• 全程数据库执行：整个查询逻辑会被EF Core翻译为SQL在数据库端完成，仅返回符合条件的单条记录，不会加载50万条数据到内存，性能大幅优化。
• 索引优化：如果为Type和ShopId建立联合索引，能进一步提升查询速度。

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