如何在ASP.NET MVC5 EF数据库优先模式下无循环映射存储过程结果

问题背景 #

我在SQL Server中有订单表、订单项表和订单日志表，通过以下存储过程查询指定ID的订单数据：

SELECT 
    table1.ID AS OrderID,
    table1.Account,
    table1.OrderNumber,
    table2.OrderID AS OrderLineItemID,
    table2.ID, table2.Price,
    orderlog.ID AS OrderLogID,
    orderlog.OrderLog
FROM 
    [dbo].[Order] AS table1
LEFT JOIN 
    OrderLineItems AS table2 ON table1.ID = table2.OrderID
LEFT JOIN 
    [dbo].[OrderLog] AS orderlog ON table1.ID = orderlog.OrderID 
WHERE
    table1.ID = @orderID

该存储过程返回扁平式关联结果，需映射到以下带集合属性的OrderView类，且要求不使用显式循环：

public class OrderView
{
    public int ID { get; set; }
    public string OrderNumber { get; set; }
    public string Account { get; set; }
    public List<OrderLineItem> OrderLineItems { get; set; }
    public List<OrderLog> OrderLogs { get; set; }
}

// 对应子实体类（结构与查询字段匹配）
public class OrderLineItem
{
    public int ID { get; set; }
    public int OrderID { get; set; }
    public decimal Price { get; set; }
}

public class OrderLog
{
    public int ID { get; set; }
    public int OrderID { get; set; }
    public string OrderLog { get; set; }
}

采用ASP.NET MVC5的Entity Framework数据库优先开发模式，请问如何实现？

解决方案 #

方法1：利用LINQ GroupBy投影（无需第三方库） #

先创建与存储过程返回字段匹配的扁平DTO：

public class OrderFlatDto
{
    public int OrderID { get; set; }
    public string Account { get; set; }
    public string OrderNumber { get; set; }
    // 订单项字段
    public int? OrderLineItemID { get; set; }
    public int? LineItemID { get; set; }
    public decimal? Price { get; set; }
    // 订单日志字段
    public int? OrderLogID { get; set; }
    public string OrderLogContent { get; set; }
}

通过EF调用存储过程，用LINQ的GroupBy分组投影：

using (var db = new YourDbContext())
{
    var flatResults = db.Database.SqlQuery<OrderFlatDto>("EXEC YourStoredProcedureName @orderID", 
        new SqlParameter("@orderID", targetOrderId)).ToList();

    var orderView = flatResults
        .GroupBy(f => f.OrderID)
        .Select(g => new OrderView
        {
            ID = g.Key,
            Account = g.First().Account,
            OrderNumber = g.First().OrderNumber,
            OrderLineItems = g.Where(item => item.LineItemID.HasValue)
                              .Select(item => new OrderLineItem
                              {
                                  ID = item.LineItemID.Value,
                                  OrderID = item.OrderID,
                                  Price = item.Price.Value
                              })
                              .Distinct()
                              .ToList(),
            OrderLogs = g.Where(item => item.OrderLogID.HasValue)
                         .Select(item => new OrderLog
                         {
                             ID = item.OrderLogID.Value,
                             OrderID = item.OrderID,
                             OrderLog = item.OrderLogContent
                         })
                         .Distinct()
                         .ToList()
        })
        .FirstOrDefault();
}

通过GroupBy按订单ID分组，再用Select投影成目标类，全程依赖LINQ操作，无手动循环。

方法2：使用AutoMapper处理分组映射 #

若项目已使用AutoMapper，可配置映射规则自动处理分组：

1. 配置AutoMapper映射：

var config = new MapperConfiguration(cfg =>
{
    cfg.CreateMap<OrderFlatDto, OrderView>()
       .ForMember(dest => dest.ID, opt => opt.MapFrom(src => src.OrderID))
       .ForMember(dest => dest.OrderLineItems, opt => opt.MapFrom(src => 
            new List<OrderLineItem> { 
                new OrderLineItem { 
                    ID = src.LineItemID.GetValueOrDefault(), 
                    OrderID = src.OrderID, 
                    Price = src.Price.GetValueOrDefault() 
                } 
            }.Where(l => l.ID != 0)))
       .ForMember(dest => dest.OrderLogs, opt => opt.MapFrom(src => 
            new List<OrderLog> { 
                new OrderLog { 
                    ID = src.OrderLogID.GetValueOrDefault(), 
                    OrderID = src.OrderID, 
                    OrderLog = src.OrderLogContent 
                } 
            }.Where(l => l.ID != 0)));

    cfg.CreateMap<IGrouping<int, OrderFlatDto>, OrderView>()
       .ForMember(dest => dest.ID, opt => opt.MapFrom(src => src.Key))
       .ForMember(dest => dest.Account, opt => opt.MapFrom(src => src.First().Account))
       .ForMember(dest => dest.OrderNumber, opt => opt.MapFrom(src => src.First().OrderNumber))
       .ForMember(dest => dest.OrderLineItems, opt => opt.MapFrom(src => 
            src.SelectMany(item => item.OrderLineItems).Distinct()))
       .ForMember(dest => dest.OrderLogs, opt => opt.MapFrom(src => 
            src.SelectMany(item => item.OrderLogs).Distinct()));
});

2. 执行映射：

var mapper = config.CreateMapper();
var flatResults = db.Database.SqlQuery<OrderFlatDto>("EXEC YourStoredProcedureName @orderID", 
    new SqlParameter("@orderID", targetOrderId)).ToList();

var orderView = flatResults
    .GroupBy(f => f.OrderID)
    .Select(g => mapper.Map<OrderView>(g))
    .FirstOrDefault();

方法3：修改存储过程返回多结果集 #

调整存储过程，返回三个独立结果集：

CREATE PROCEDURE GetOrderWithDetails
    @orderID INT
AS
BEGIN
    -- 返回订单主数据
    SELECT ID, Account, OrderNumber FROM [dbo].[Order] WHERE ID = @orderID;
    -- 返回订单项
    SELECT ID, OrderID, Price FROM OrderLineItems WHERE OrderID = @orderID;
    -- 返回订单日志
    SELECT ID, OrderID, OrderLog FROM [dbo].[OrderLog] WHERE OrderID = @orderID;
END

通过EF的SqlQuery分别读取结果集并组装：

using (var db = new YourDbContext())
{
    var cmd = db.Database.Connection.CreateCommand();
    cmd.CommandText = "EXEC GetOrderWithDetails @orderID";
    cmd.Parameters.Add(new SqlParameter("@orderID", targetOrderId));

    db.Database.Connection.Open();
    var reader = cmd.ExecuteReader();

    // 读取订单主数据
    var order = db.Database.SqlQuery<OrderView>(reader).FirstOrDefault();
    reader.NextResult();

    // 读取订单项
    order.OrderLineItems = db.Database.SqlQuery<OrderLineItem>(reader).ToList();
    reader.NextResult();

    // 读取订单日志
    order.OrderLogs = db.Database.SqlQuery<OrderLog>(reader).ToList();

    db.Database.Connection.Close();
}

此方式避免JOIN产生的重复数据，无需分组，直接通过多结果集组装目标类。

内容的提问来源于stack exchange，提问作者Ahsan Ali Siddiqi