C#中如何将数据库查询结果映射至嵌套实体对象？

问题背景 #

前端期望后端返回的JSON结构如下：

{
  "unBookedOrders": [
    {
      "orderId": 865470,
      "orderCollectionNumber": 6114353,
      "orderAddress": {
        "address": "Gamlestadsvägen 123",
        "city": "Göteborg"
      },
      "customer": {
        "firmName": "Firm Name",
        "agentName": "Agent Name"
      }
    }
  ]
}

后端已定义对应的嵌套C#实体类：

public class Order
{
    public int orderId { get; set; }
    public int orderCollectionNumber { get; set; }
    public OrderAddress orderAddress { get; set; }
    public Customer customer { get; set; }
}

public class OrderAddress
{
    public string address { get; set; }
    public string city { get; set; }
}

public class Customer
{
    public string firmName { get; set; }
    public string agentName { get; set; }
}

目前数据库查询能正常映射orderId和orderCollectionNumber，但需要把查询返回的地址、客户字段映射到OrderAddress和Customer嵌套对象中，当前查询语句片段如下：

var orders = await _session.QueryAsync<Orders>(@"
    SELECT
        o.Id AS OrderId,
        oc.OrderCollectionNumber,
        bo.AddressLine1 AS Address,
        bo.AddressCity AS City,
        c.FirstName,
        c.AgentName
    from Table1 opp ...
    WHERE ...
");

解决方案 #

方案1：手动映射（简单直接） #

适合小数据量场景，先把查询结果映射到扁平DTO，再手动转换为嵌套实体：

1. 定义扁平DTO，对应查询返回的所有字段：

public class OrderFlatDto
{
    public int OrderId { get; set; }
    public int OrderCollectionNumber { get; set; }
    public string Address { get; set; }
    public string City { get; set; }
    public string FirmName { get; set; }
    public string AgentName { get; set; }
}

2. 修改查询语句，给c.FirstName添加别名FirmName，对应DTO的字段：

var flatOrders = await _session.QueryAsync<OrderFlatDto>(@"
    SELECT
        o.Id AS OrderId,
        oc.OrderCollectionNumber,
        bo.AddressLine1 AS Address,
        bo.AddressCity AS City,
        c.FirstName AS FirmName,
        c.AgentName
    from Table1 opp ...
    WHERE ...
");

3. 手动转换为目标嵌套实体：

var mappedOrders = flatOrders.Select(flat => new Order
{
    orderId = flat.OrderId,
    orderCollectionNumber = flat.OrderCollectionNumber,
    orderAddress = new OrderAddress
    {
        address = flat.Address,
        city = flat.City
    },
    customer = new Customer
    {
        firmName = flat.FirmName,
        agentName = flat.AgentName
    }
}).ToList();

方案2：用AutoMapper自动映射 #

如果项目已经引入AutoMapper，可以配置映射规则实现自动转换：

1. 先定义好扁平DTO（同方案1），然后配置AutoMapper映射规则：

var mapperConfig = new MapperConfiguration(cfg =>
{
    cfg.CreateMap<OrderFlatDto, Order>()
        .ForMember(dest => dest.orderAddress, opt => opt.MapFrom(src => new OrderAddress
        {
            address = src.Address,
            city = src.City
        }))
        .ForMember(dest => dest.customer, opt => opt.MapFrom(src => new Customer
        {
            firmName = src.FirmName,
            agentName = src.AgentName
        }));
});

IMapper mapper = mapperConfig.CreateMapper();

2. 执行查询并自动映射：

var flatOrders = await _session.QueryAsync<OrderFlatDto>(/* 调整后的查询语句 */);
var mappedOrders = mapper.Map<List<Order>>(flatOrders);

方案3：利用ORM的投影功能（以NHibernate为例） #

如果使用的是NHibernate这类ORM，可以直接通过投影查询构造嵌套实体，无需中间DTO：

var mappedOrders = await _session.QueryOver<Table1>()
    // 关联所需表，根据实际表关系调整
    .JoinAlias(opp => opp.Order, () => o)
    .JoinAlias(opp => opp.OrderCollection, () => oc)
    .JoinAlias(opp => opp.BillingAddress, () => bo)
    .JoinAlias(opp => opp.Customer, () => c)
    // 添加查询条件
    .Where(/* 你的WHERE条件 */)
    // 投影字段并指定别名，要和实体属性名完全匹配
    .SelectList(list => list
        .Select(() => o.Id).WithAlias(() => orderEntity.orderId)
        .Select(() => oc.OrderCollectionNumber).WithAlias(() => orderEntity.orderCollectionNumber)
        .Select(() => bo.AddressLine1).WithAlias(() => orderEntity.orderAddress.address)
        .Select(() => bo.AddressCity).WithAlias(() => orderEntity.orderAddress.city)
        .Select(() => c.FirstName).WithAlias(() => orderEntity.customer.firmName)
        .Select(() => c.AgentName).WithAlias(() => orderEntity.customer.agentName)
    )
    // 用别名自动映射到实体
    .TransformUsing(Transformers.AliasToBean<Order>())
    .ListAsync<Order>();

注意：别名必须和实体的属性名（包括大小写）完全一致，具体取决于你的ORM大小写匹配配置。

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