使用ADO.NET调用存储过程读取数据时如何映射外键关联的Dependent对象集合

1. 现有代码问题梳理 #

• 存储过程使用INNER JOIN会过滤没有关联Dependent的Employee，按需可以改为LEFT JOIN保留全量员工数据
• SELECT *会导致Employee和Dependent的Name、Id字段重名，DataReader读取时会出现取值错误
• 仓储的GetAll方法返回值错误，应该返回List<Employee>而不是单个Employee对象
• 现有读取逻辑只取第一行数据，没有遍历全部返回结果，也没有做员工去重处理

2. 修正后的存储过程 #

Create procedure spGetEmployeeAll 
as 
begin 
-- 给重名字段加别名避免读取冲突
Select 
    e.Id as EmployeeId,
    e.Name as EmployeeName,
    d.Id as DependentId,
    d.Name as DependentName,
    d.EmployeeId as DependentEmployeeId
from Employee e
left join Dependent d
on e.Id = d.EmployeeId
end 

3. 修正后的仓储映射代码 #

public class EmployeeRepository : IEmployeeRepository
{
    private readonly string _connectionString;
    public EmployeeRepository(IConfiguration configuration)
    {
        _connectionString = configuration.GetConnectionString("TestDatabase");
    }

    // 返回值调整为员工集合
    public List<Employee> GetAll()
    {
        // 用字典做员工去重，key为员工ID，value为员工对象
        var employeeDict = new Dictionary<int, Employee>();
        using (SqlConnection sql = new SqlConnection(_connectionString))
        {
            using (SqlCommand cmd = new SqlCommand("spGetEmployeeAll", sql))
            {
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                sql.Open();
                using (var reader = cmd.ExecuteReader())
                {
                    // 遍历所有返回行
                    while (reader.Read())
                    {
                        int employeeId = Convert.ToInt32(reader["EmployeeId"]);
                        // 未缓存过该员工则先初始化
                        if (!employeeDict.ContainsKey(employeeId))
                        {
                            var employee = new Employee
                            {
                                Id = employeeId,
                                Name = Convert.ToString(reader["EmployeeName"]),
                                // 初始化外键集合
                                Dependent = new List<Dependent>()
                            };
                            employeeDict.Add(employeeId, employee);
                        }
                        //  LEFT JOIN可能返回空家属数据，先判空再映射
                        if (!reader.IsDBNull(reader.GetOrdinal("DependentId")))
                        {
                            var dependent = new Dependent
                            {
                                Id = Convert.ToInt32(reader["DependentId"]),
                                EmployeeId = Convert.ToInt32(reader["DependentEmployeeId"]),
                                Name = Convert.ToString(reader["DependentName"])
                            };
                            // 追加到对应员工的外键集合
                            employeeDict[employeeId].Dependent.Add(dependent);
                        }
                    }
                }
                // 无需手动关闭连接，using代码块结束会自动释放资源
            }
        }
        return employeeDict.Values.ToList();
    }
}

4. 核心逻辑说明 #

• 用字典存储已读取的员工，解决关联多个Dependent时同一个员工返回多行重复数据的问题
• 遍历DataReader时优先处理员工主体数据，再将当前行的Dependent数据追加到对应员工的集合中
• 字段别名解决多表同名字段冲突，空值判断避免LEFT JOIN返回的空数据报错

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