如何在Entity Framework中实现返回多表合并数据的分页API？

1. 创建统一展示DTO #

首先需要定义一个包含两个实体所有需展示字段的统一DTO，同时添加类型标识让前端区分记录来源：

// 记录类型枚举，用于区分是账户还是项目实体
public enum RecordTypeEnum
{
    Account,
    ProjectEntity
}

// 统一返回的DTO
public class UnifiedRecordDto
{
    // BaseEntity公共字段
    public Guid Id { get; set; }
    public DateTime CreatedDate { get; set; }
    
    // 类型标识
    public RecordTypeEnum RecordType { get; set; }
    
    // AccountEntity专属字段（用可空类型避免空值问题）
    public long? AccountNumber { get; set; }
    public AccountTypeEnum? AccountType { get; set; }
    public bool? Privacy { get; set; }
    
    // ProjectEntitiesEntity专属字段
    public string? Name { get; set; }
    public ProjectEntitiesEnum? ProjectType { get; set; }
    public bool? Status { get; set; }
}

2. 重构查询与分页逻辑 #

修正原代码中仅统计单表数据、分页逻辑错误的问题，直接在EF查询中映射到统一DTO，再合并分页：

public async Task<PaginatedResponseVM<UnifiedRecordDto>> GetMergedRecordsList(DataParameters parameters)
{
    var pageIndex = parameters?.PageNumber ?? 1;
    var pageSize = parameters?.PageSize ?? 10;

    // 查询项目实体并映射到统一DTO
    var projectQuery = _context.ProjectEntities
        .Include(k => k.ContactAddress)
        .OrderByDescending(x => x.CreatedDate)
        .Select(x => new UnifiedRecordDto
        {
            Id = x.Id,
            CreatedDate = x.CreatedDate,
            RecordType = RecordTypeEnum.ProjectEntity,
            Name = x.Name,
            ProjectType = x.Type,
            Status = x.Status
            // 如需ContactAddress字段，在此补充映射
        });

    // 查询符合条件的账户并映射到统一DTO
    var accountQuery = _context.Accounts
        .Include(x => x.AccountManagerAddress)
        .OrderByDescending(x => x.CreatedDate)
        .Where(x => x.AccountType == AccountTypeEnum.ProjectProponent)
        .Select(x => new UnifiedRecordDto
        {
            Id = x.Id,
            CreatedDate = x.CreatedDate,
            RecordType = RecordTypeEnum.Account,
            AccountNumber = x.AccountNumber,
            AccountType = x.AccountType,
            Privacy = x.Privacy
            // 如需AccountManagerAddress字段，在此补充映射
        });

    // 合并两个查询并按创建时间降序排序
    var mergedQuery = projectQuery.Concat(accountQuery).OrderByDescending(dto => dto.CreatedDate);

    // 获取合并后的总记录数
    var totalCount = await mergedQuery.CountAsync();

    // 分页获取最终数据
    var paginatedItems = await mergedQuery
        .Skip((pageIndex - 1) * pageSize)
        .Take(pageSize)
        .ToListAsync();

    return new PaginatedResponseVM<UnifiedRecordDto>(totalCount, paginatedItems);
}

3. 可选：用AutoMapper简化映射 #

如果项目中使用AutoMapper，可添加映射配置替代手动Select，提升代码维护性：

public class MappingProfile : Profile
{
    public MappingProfile()
    {
        // AccountEntity到UnifiedRecordDto的映射
        CreateMap<AccountEntity, UnifiedRecordDto>()
            .ForMember(dest => dest.RecordType, opt => opt.MapFrom(_ => RecordTypeEnum.Account))
            .ForMember(dest => dest.Name, opt => opt.Ignore())
            .ForMember(dest => dest.ProjectType, opt => opt.Ignore())
            .ForMember(dest => dest.Status, opt => opt.Ignore());

        // ProjectEntitiesEntity到UnifiedRecordDto的映射
        CreateMap<ProjectEntitiesEntity, UnifiedRecordDto>()
            .ForMember(dest => dest.RecordType, opt => opt.MapFrom(_ => RecordTypeEnum.ProjectEntity))
            .ForMember(dest => dest.AccountNumber, opt => opt.Ignore())
            .ForMember(dest => dest.AccountType, opt => opt.Ignore())
            .ForMember(dest => dest.Privacy, opt => opt.Ignore());
    }
}

之后查询代码可简化为：

var projectQuery = _context.ProjectEntities
    .Include(k => k.ContactAddress)
    .OrderByDescending(x => x.CreatedDate)
    .ProjectTo<UnifiedRecordDto>(_mapper.ConfigurationProvider);

var accountQuery = _context.Accounts
    .Include(x => x.AccountManagerAddress)
    .OrderByDescending(x => x.CreatedDate)
    .Where(x => x.AccountType == AccountTypeEnum.ProjectProponent)
    .ProjectTo<UnifiedRecordDto>(_mapper.ConfigurationProvider);

4. 前端处理逻辑 #

前端拿到UnifiedRecordDto数组后，根据RecordType字段判断当前行类型，动态渲染对应列：

• 若为Account类型，展示AccountNumber、AccountType、Privacy字段
• 若为ProjectEntity类型，展示Name、ProjectType、Status字段
• 公共字段（如CreatedDate、Id）始终展示

内容的提问来源于stack exchange，提问作者saurav.rox