ASP.NET Core Web API中如何用EF Core调用多表连接存储过程并映射嵌套关联数据

1. 定义相关实体与扁平化接收DTO #

存储过程返回的是三表inner join后的扁平化行数据，需要先定义业务实体，再定义专门接收存储过程返回结果的无键DTO：

// 部门实体
public class Department
{
    public int DepartmentId { get; set; }
    public string DepartmentName { get; set; }
    public List<Employee> Employees { get; set; } = new();
}

// 员工实体
public class Employee
{
    public int EmployeeId { get; set; }
    public string EmployeeName { get; set; }
    public int DepartmentId { get; set; }
    public List<Address> Addresses { get; set; } = new();
}

// 地址实体
public class Address
{
    public int AddressId { get; set; }
    public string Detail { get; set; }
    public int EmployeeId { get; set; }
}

// 专门接收存储过程返回的扁平化join结果
public class DeptEmpAddrSPResult
{
    // 部门字段
    public int DepartmentId { get; set; }
    public string DepartmentName { get; set; }
    // 员工字段
    public int EmployeeId { get; set; }
    public string EmployeeName { get; set; }
    // 地址字段
    public int AddressId { get; set; }
    public string AddressDetail { get; set; }
}

2. 配置DbContext #

将扁平化DTO注册为无键实体，不映射到数据库物理表，仅用于接收存储过程返回结果：

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options)
    {
    }

    // 注册无键实体集
    public DbSet<DeptEmpAddrSPResult> DeptEmpAddrSPResults { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // 配置为无键实体
        modelBuilder.Entity<DeptEmpAddrSPResult>().HasNoKey();
    }
}

3. 调用存储过程并组装嵌套结构 #

先调用存储过程拿到所有扁平化结果，再在内存中按层级分组组装为嵌套结构：

public async Task<List<Department>> GetDepartmentWithEmpAndAddrAsync()
{
    // 1. 调用存储过程获取扁平化结果，有参数可使用占位符写法避免SQL注入，例如：
    // FromSqlRaw("Exec GetDeptEmpAddr @DeptId={0}", deptId)
    var flatResults = await _context.DeptEmpAddrSPResults
        .FromSqlRaw("Exec 你的存储过程名称")
        .ToListAsync();

    // 2. 内存分组组装嵌套结构
    var result = flatResults
        // 第一层按部门分组
        .GroupBy(r => new { r.DepartmentId, r.DepartmentName })
        .Select(deptGroup => new Department
        {
            DepartmentId = deptGroup.Key.DepartmentId,
            DepartmentName = deptGroup.Key.DepartmentName,
            // 第二层按员工分组
            Employees = deptGroup
                .GroupBy(r => new { r.EmployeeId, r.EmployeeName })
                .Select(empGroup => new Employee
                {
                    EmployeeId = empGroup.Key.EmployeeId,
                    EmployeeName = empGroup.Key.EmployeeName,
                    DepartmentId = deptGroup.Key.DepartmentId,
                    // 第三层组装地址列表
                    Addresses = empGroup.Select(a => new Address
                    {
                        AddressId = a.AddressId,
                        Detail = a.AddressDetail,
                        EmployeeId = empGroup.Key.EmployeeId
                    }).ToList()
                }).ToList()
        }).ToList();

    return result;
}

注意事项 #

• 存储过程传参必须使用带占位符的写法，禁止直接拼接SQL字符串，避免注入风险
• 扁平化DTO的字段名、字段类型必须和存储过程返回的列完全对应，否则会出现映射失败问题
• 数据量特别大的场景不建议全量加载后内存分组，可考虑拆分存储过程或分页处理

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