如何使用AutoMapper将多行数据库记录映射到单个EmployeeDetails对象

Absolutely, you can map these scattered rows to a single EmployeeDetails object with AutoMapper—but you’ll need to adjust your approach slightly. Instead of mapping each DataRow directly to EmployeeDetails, we’ll first map rows to a flat DTO that matches your table structure, group those DTOs by EmpNo, then map each group to your nested EmployeeDetails class. Here’s how to do it step by step:

1. Create a Flat DTO for Database Rows #

First, define a class that mirrors your database table columns. This makes mapping individual rows straightforward:

public class EmployeeRow
{
    public string EmpNo { get; set; }
    public string Name { get; set; }
    public string Address_1 { get; set; }
    public string City { get; set; }
    public string PhoneType { get; set; }
    public string PhoneNo { get; set; }
}

2. Configure AutoMapper Profiles #

Next, set up your AutoMapper profile to handle three key mappings:

• DataRow → EmployeeRow (auto-mapped since column names match)
• EmployeeRow → Address and EmployeeRow → Phone (direct property matches)
• Grouped IEnumerable<EmployeeRow> → EmployeeDetails (combines the group into a single object)

public class EmployeeMappingProfile : Profile
{
    public EmployeeMappingProfile()
    {
        // Map DataRow to flat EmployeeRow (AutoMapper handles this automatically)
        CreateMap<DataRow, EmployeeRow>();

        // Map EmployeeRow to Address (matches Address_1 and City properties)
        CreateMap<EmployeeRow, Address>();

        // Map EmployeeRow to Phone (matches PhoneType and PhoneNo properties)
        CreateMap<EmployeeRow, Phone>();

        // Map grouped EmployeeRows to a single EmployeeDetails
        CreateMap<IEnumerable<EmployeeRow>, EmployeeDetails>()
            // Use EmpNo from any row in the group (all are same)
            .ForMember(dest => dest.EmpNo, opt => opt.MapFrom(src => src.First().EmpNo))
            // Grab the first non-null Name from the group
            .ForMember(dest => dest.Name, opt => opt.MapFrom(src => src.First(row => !string.IsNullOrEmpty(row.Name)).Name))
            // Map Address from the first row with non-null address data
            .ForMember(dest => dest.Address, opt => opt.MapFrom(src => src.First(row => !string.IsNullOrEmpty(row.Address_1) || !string.IsNullOrEmpty(row.City))))
            // Collect all non-null phone entries into the Phone list
            .ForMember(dest => dest.Phone, opt => opt.MapFrom(src => src.Where(row => !string.IsNullOrEmpty(row.PhoneType) && !string.IsNullOrEmpty(row.PhoneNo))));
    }
}

3. Modify Your Data Access Code #

Update your existing method to first map rows to EmployeeRow, group by EmpNo, then map each group to EmployeeDetails:

public IEnumerable<EmployeeDetails> GetEmployeeDetails()
{
    // Step 1: Map all DataRows to flat EmployeeRow objects
    var employeeRows = ExecuteEmpReader<EmployeeRow>().ToList();

    // Step 2: Group rows by EmpNo to combine data for the same employee
    var groupedEmployees = employeeRows.GroupBy(row => row.EmpNo);

    // Step 3: Map each group to a single EmployeeDetails object
    return groupedEmployees.Select(group => _mapper.Map<EmployeeDetails>(group));
}

// Your existing ExecuteEmpReader method remains unchanged (it's generic)
private IEnumerable<T> ExecuteEmpReader<T>()
{
    DataTable dt = new DataTable();
    // Assume dt is loaded with your table data
    foreach (DataRow item in dt.Rows)
    {
        yield return _mapper.Map<T>(item);
    }
}

Key Notes #

• Why This Works: By grouping first, we can aggregate the scattered data (name/address in one row, phones in others) into a single object. AutoMapper uses LINQ expressions to pull the relevant data from the group.
• Handling Edge Cases: The First(row => ...) and Where(row => ...) clauses ensure we only use non-null data. If your data might have multiple address rows, adjust the First() logic to pick the correct one (e.g., most recent, primary address).
• AutoMapper Setup: Don’t forget to register your profile when initializing AutoMapper (e.g., new MapperConfiguration(cfg => cfg.AddProfile<EmployeeMappingProfile>())).

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