Entity Framework Core：基于一对多关联首条记录的筛选与排序

实体定义 #

• Registration：包含UserName、FirstName、MiddleName、LastName、Email等字段
• RegistrationAction：包含RegistrationId（关联外键）、ActionTaken、CreatedDate等字段

需求描述 #

需要开发页面展示所有Registration记录及其最新的ActionTaken，同时支持：

1. 基于最新的ActionTaken筛选Registration记录
2. 对Registration的字段（如用户名、邮箱）以及最新的ActionTaken进行排序

现有代码（待完善） #

private async Task LoadData(int? pageIndex, int? pageSize, int? prevPageSize, string? filter, string? state)
{
    var extraFilter = DecodeState(state);

    var query = DbContext.Registrations.AsQueryable();

    // 仅加载最新的RegistrationAction记录
    // 需将此关联用于下方(1)的筛选和(2)的排序逻辑
    query = query.Include(x => x.RegistrationActions.OrderByDescending(x => x.CreatedDate).Take(1));

    if (!string.IsNullOrEmpty(filter))
    {
        query = query.Where(x => x.UserName.Contains(filter) || x.FirstName.Contains(filter) || (x.MiddleName != null
            && x.MiddleName.Contains(filter)) || x.LastName.Contains(filter) || x.Email.Contains(filter));
    }

    if (!string.IsNullOrEmpty(extraFilter) && Enum.TryParse(extraFilter, out RegistrationActionState actionState))
    {
        // (1) TODO: 基于最新的RegistrationAction.ActionTaken筛选主表Registration
        query = ???;
    }

    if (SortField == nameof(Registration.FullName))
    {
        query = CurrentSortOrder == SortOrder.Ascending ? query.OrderBy(x => x.LastName).ThenBy(x => x.FirstName).ThenBy(x => x.MiddleName) :
            query.OrderByDescending(x => x.LastName).ThenByDescending(x => x.FirstName).ThenByDescending(x => x.MiddleName);
    }
    else if (SortField == nameof(Registration.UserName))
    {
        query = CurrentSortOrder == SortOrder.Ascending ? query.OrderBy(x => x.UserName) : query.OrderByDescending(x => x.UserName);
    }
    else if (SortField == nameof(Registration.Email))
    {
        query = CurrentSortOrder == SortOrder.Ascending ? query.OrderBy(x => x.Email) : query.OrderByDescending(x => x.Email);
    }
    else if (SortField == nameof(RegistrationAction.ActionTaken))
    {
        // (2) TODO: 基于最新的RegistrationAction.ActionTaken对主表Registration排序
        query = ???;
    }

    await OnGetAsync(query, pageIndex, pageSize, prevPageSize, filter);

    RegistrationViewModels = Entities.Select(UserRegistrationsPage.ConvertToViewModel).ToList();
}

核心问题 #

1. 直接在Include后加Where只会过滤关联的RegistrationAction数据，不会筛选主表Registration
2. 尝试手动Join最新的RegistrationAction但未成功，需要正确关联最新记录来实现筛选和排序

期望生成的SQL示例 #

SELECT [t].[Id], [t].[UserName], [t0].[Id], [t0].[ActionTaken], [t0].[RegistrationId]
FROM (
    SELECT [r].[Id], [r].[UserName]
    FROM [auth].[Registration] AS [r]
) AS [t]
LEFT JOIN (
    SELECT [t1].[Id], [t1].[ActionTaken], [t1].[CreatedDate], [t1].[RegistrationId]
    FROM (
        SELECT [r0].[Id], [r0].[ActionTaken], [r0].[CreatedDate], [r0].[RegistrationId], 
        ROW_NUMBER() OVER(PARTITION BY [r0].[RegistrationId] ORDER BY [r0].[CreatedDate] DESC) AS [row]
        FROM [auth].[RegistrationAction] AS [r0]
    ) AS [t1]
    WHERE [t1].[row] <= 1
) AS [t0] ON [t].[Id] = [t0].[RegistrationId]
-- 筛选条件示例：WHERE [t0].[ActionTaken] = 'Approved'
-- 排序示例：ORDER BY [t0].ActionTaken

解决方案 #

要实现基于最新关联记录的筛选和排序，不能仅依赖Include，需要先通过子查询获取每个Registration的最新RegistrationAction，再将其与主表关联。以下是修改后的代码：

步骤1：定义获取最新Action的子查询 #

使用ROW_NUMBER()函数精准匹配每个Registration的最新操作记录，和你期望的SQL结构一致：

var latestActionsQuery = from ra in DbContext.RegistrationActions
                         select new
                         {
                             ra.RegistrationId,
                             ra.ActionTaken,
                             RowNumber = EF.Functions.RowNumber()
                                 .Over()
                                 .PartitionBy(ra.RegistrationId)
                                 .OrderByDescending(ra => ra.CreatedDate)
                         } into ranked
                         where ranked.RowNumber == 1
                         select ranked;

步骤2：重构主查询实现筛选与排序 #

将主表与最新Action子查询关联，替换原有逻辑：

private async Task LoadData(int? pageIndex, int? pageSize, int? prevPageSize, string? filter, string? state)
{
    var extraFilter = DecodeState(state);

    // 子查询：获取每个Registration的最新Action记录
    var latestActionsQuery = from ra in DbContext.RegistrationActions
                             select new
                             {
                                 ra.RegistrationId,
                                 ra.ActionTaken,
                                 RowNumber = EF.Functions.RowNumber()
                                     .Over()
                                     .PartitionBy(ra.RegistrationId)
                                     .OrderByDescending(ra => ra.CreatedDate)
                             } into ranked
                             where ranked.RowNumber == 1
                             select ranked;

    // 主查询：左连接主表与最新Action，兼容无Action的Registration
    var query = from r in DbContext.Registrations
                join latestAction in latestActionsQuery on r.Id equals latestAction.RegistrationId into latestActionGroup
                from latestAction in latestActionGroup.DefaultIfEmpty()
                select new { Registration = r, LatestAction = latestAction };

    // 通用文本筛选
    if (!string.IsNullOrEmpty(filter))
    {
        query = query.Where(x => x.Registration.UserName.Contains(filter) || 
                                 x.Registration.FirstName.Contains(filter) || 
                                 (x.Registration.MiddleName != null && x.Registration.MiddleName.Contains(filter)) || 
                                 x.Registration.LastName.Contains(filter) || 
                                 x.Registration.Email.Contains(filter));
    }

    // 基于最新ActionTaken的筛选
    if (!string.IsNullOrEmpty(extraFilter) && Enum.TryParse(extraFilter, out RegistrationActionState actionState))
    {
        query = query.Where(x => x.LatestAction != null && x.LatestAction.ActionTaken == actionState);
    }

    // 排序逻辑
    if (SortField == nameof(Registration.FullName))
    {
        query = CurrentSortOrder == SortOrder.Ascending 
            ? query.OrderBy(x => x.Registration.LastName).ThenBy(x => x.Registration.FirstName).ThenBy(x => x.Registration.MiddleName) 
            : query.OrderByDescending(x => x.Registration.LastName).ThenByDescending(x => x.Registration.FirstName).ThenByDescending(x => x.Registration.MiddleName);
    }
    else if (SortField == nameof(Registration.UserName))
    {
        query = CurrentSortOrder == SortOrder.Ascending 
            ? query.OrderBy(x => x.Registration.UserName) 
            : query.OrderByDescending(x => x.Registration.UserName);
    }
    else if (SortField == nameof(Registration.Email))
    {
        query = CurrentSortOrder == SortOrder.Ascending 
            ? query.OrderBy(x => x.Registration.Email) 
            : query.OrderByDescending(x => x.Registration.Email);
    }
    else if (SortField == nameof(RegistrationAction.ActionTaken))
    {
        query = CurrentSortOrder == SortOrder.Ascending 
            ? query.OrderBy(x => x.LatestAction?.ActionTaken) 
            : query.OrderByDescending(x => x.LatestAction?.ActionTaken);
    }

    // 映射回Registration实体并保留Include（用于页面展示关联数据）
    var registrationQuery = query.Select(x => x.Registration)
                                 .Include(x => x.RegistrationActions.OrderByDescending(ra => ra.CreatedDate).Take(1));

    await OnGetAsync(registrationQuery, pageIndex, pageSize, prevPageSize, filter);

    RegistrationViewModels = Entities.Select(UserRegistrationsPage.ConvertToViewModel).ToList();
}

关键说明 #

• 左连接DefaultIfEmpty()确保没有任何操作记录的Registration也能被查询到
• 筛选和排序直接基于关联的最新Action字段，确保主表数据被正确过滤和排序
• 子查询使用ROW_NUMBER()和你期望的SQL逻辑完全一致，性能更稳定

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