EF Core 7+PostgreSQL：如何用Skip/Take获取每个客户最新消息

针对百万级客户数据、每个客户仅带最新一条消息的分页查询需求，以下是两种高效实现方式，均能避免全表扫描和N+1查询问题：

方案一：分组子查询关联 #

先通过分组子查询获取每个客户的最新消息，再与客户表关联后执行分页：

int skip = 0;
int take = 2;

var query = from customer in _context.Customers
            // 子查询：按客户分组，取每组最新消息
            join latestMsgGroup in (
                from msg in _context.CustomerMessages
                group msg by msg.CustomerId into customerMsgGroup
                select new
                {
                    CustomerId = customerMsgGroup.Key,
                    LatestMessage = customerMsgGroup.OrderByDescending(m => m.CreatedOnUtc).FirstOrDefault()
                }
            ) on customer.Id equals latestMsgGroup.CustomerId into customerWithMsg
            from latestMsg in customerWithMsg.DefaultIfEmpty()
            // 按客户ID倒序，匹配示例期望结果的排序逻辑
            orderby customer.Id descending
            select new
            {
                customer.Id,
                // 拼接FirstName和LastName为Name，对齐示例表结构
                Name = $"{customer.FirstName} {customer.LastName}",
                CustomerMessages = latestMsg?.LatestMessage != null 
                    ? new List<object> { new { Message = latestMsg.LatestMessage.Message } } 
                    : new List<object>()
            };

var result = await query.Skip(skip).Take(take).ToListAsync();

方案二：PostgreSQL窗口函数（推荐大数据量场景） #

利用PostgreSQL的ROW_NUMBER()窗口函数高效筛选每个客户的最新消息，再关联分页：

int skip = 0;
int take = 2;

// 窗口函数筛选：按客户分组，消息时间倒序，取行号为1的最新消息
var latestMessagesQuery = _context.CustomerMessages
    .Select(msg => new
    {
        msg.CustomerId,
        msg.Message,
        RowNum = EF.Functions.RowNumber().Over(
            partitionBy: msg.CustomerId,
            orderBy: msg.CreatedOnUtc descending)
    })
    .Where(r => r.RowNum == 1);

var query = from customer in _context.Customers
            join latestMsg in latestMessagesQuery on customer.Id equals latestMsg.CustomerId into customerWithMsg
            from latestMsg in customerWithMsg.DefaultIfEmpty()
            orderby customer.Id descending
            select new
            {
                customer.Id,
                Name = $"{customer.FirstName} {customer.LastName}",
                CustomerMessages = latestMsg != null 
                    ? new List<object> { new { latestMsg.Message } } 
                    : new List<object>()
            };

var result = await query.Skip(skip).Take(take).ToListAsync();

性能优化建议 #

1. 创建复合索引：为CustomerMessage表创建针对CustomerId和CreatedOnUtc的复合索引，大幅提升分组/窗口函数的查询效率：

   CREATE INDEX IX_CustomerMessage_CustomerId_CreatedOnUtc ON "CustomerMessage" ("CustomerId", "CreatedOnUtc" DESC);
   

2. 避免大数值Skip：如果分页深度较大（如Skip 10万+），建议改用键集分页（记录上一页最后一个客户ID，通过Where Id < lastId替代Skip），避免PostgreSQL扫描大量前置数据。
3. 按需选择字段：只查询业务需要的字段，避免加载实体中不必要的属性，减少数据传输量。

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