ASP.NET Core 2.0：如何用存储过程实现实体预加载？

好问题！在ASP.NET Core 2.0搭配EF Core 2.0的场景下，确实可以通过存储过程返回的结果集来半自动绑定父对象和它的关联对象，但具体实现要看你用的是单结果集（查询A）还是多结果集（查询B）的存储过程写法，两种方式的处理逻辑差异不小，我给你详细拆解一下：

首先先明确你给出的数据模型定义：

public class Address 
{ 
    public int Id {get;set;} 
    public string Street1 {get;set;} 
    public string Street2 {get;set;} 
    public string City {get;set;} 
    public string Region {get;set;} 
    public string PostalCode {get;set;} 
    public string Country {get;set;} 
} 

public class Product 
{ 
    public int Id {get;set;} 
    public string Name {get;set;} 
    public decimal Price {get;set;} 
} 

public class Customer 
{ 
    public int Id {get;set;} 
    public string FirstName {get;set;} 
    public string LastName {get;set;} 
    public Address ShippingAddress {get;set;} 
    public Address BillingAddress {get;set;} 
    public List<Product> Products {get;set;} 
} 

一、查询A：单扁平化结果集的处理方式 #

EF Core 2.0本身不支持直接从单扁平化结果集自动拆分并填充嵌套关联对象——也就是说，你直接用dbContext.Set<Customer>().FromSql(...)是不行的，因为FromSql只能映射到Customer的直接属性，没法识别CustomerShippingAddressId这种前缀来自动填充ShippingAddress嵌套对象。

要实现这种场景，你需要做两步：

1. 先定义一个扁平化的DTO，对应存储过程返回的所有字段；
2. 手动将DTO列表分组、转换为Customer对象及其关联属性。

具体实现步骤： #

1. 定义扁平化DTO

public class CustomerFlatDto
{
    public int CustomerId { get; set; }
    public string CustomerFirstName { get; set; }
    public string CustomerLastName { get; set; }

    // 收货地址字段
    public int CustomerShippingAddressId { get; set; }
    public string CustomerShippingAddressStreet1 { get; set; }
    public string CustomerShippingAddressStreet2 { get; set; }
    public string CustomerShippingAddressCity { get; set; }
    public string CustomerShippingAddressPostalCode { get; set; }
    public string CustomerShippingAddressCountry { get; set; }

    // 账单地址字段
    public int CustomerBillingAddressId { get; set; }
    public string CustomerBillingAddressStreet1 { get; set; }
    public string CustomerBillingAddressStreet2 { get; set; }
    public string CustomerBillingAddressCity { get; set; }
    public string CustomerBillingAddressPostalCode { get; set; }
    public string CustomerBillingAddressCountry { get; set; }

    // 商品字段（允许为null，因为是左连接）
    public int? ProductId { get; set; }
    public string ProductName { get; set; }
    public decimal? ProductPrice { get; set; }
}

2. 调用存储过程并手动转换

// 注意：EF Core 2.0中如果要查询非实体的DTO，需要先在DbContext中注册查询
// 可以在DbContext里添加：public DbQuery<CustomerFlatDto> CustomerFlatDtos { get; set; }

var flatResults = dbContext.CustomerFlatDtos
    .FromSql("EXEC dbo.[GetCustomerInfo] @TokenDtime={0}", tokenDtime)
    .ToList();

// 分组转换为Customer对象，处理重复数据（因为左连接商品会导致Customer重复）
var customers = flatResults
    .GroupBy(f => f.CustomerId)
    .Select(g => new Customer
    {
        Id = g.Key,
        FirstName = g.First().CustomerFirstName,
        LastName = g.First().CustomerLastName,
        ShippingAddress = new Address
        {
            Id = g.First().CustomerShippingAddressId,
            Street1 = g.First().CustomerShippingAddressStreet1,
            Street2 = g.First().CustomerShippingAddressStreet2,
            City = g.First().CustomerShippingAddressCity,
            PostalCode = g.First().CustomerShippingAddressPostalCode,
            Country = g.First().CustomerShippingAddressCountry
        },
        BillingAddress = new Address
        {
            Id = g.First().CustomerBillingAddressId,
            Street1 = g.First().CustomerBillingAddressStreet1,
            Street2 = g.First().CustomerBillingAddressStreet2,
            City = g.First().CustomerBillingAddressCity,
            PostalCode = g.First().CustomerBillingAddressPostalCode,
            Country = g.First().CustomerBillingAddressCountry
        },
        Products = g.Where(p => p.ProductId.HasValue)
            .Select(p => new Product
            {
                Id = p.ProductId.Value,
                Name = p.ProductName,
                Price = p.ProductPrice.Value
            })
            .DistinctBy(p => p.Id) // 去重，避免重复商品
            .ToList()
    })
    .ToList();

二、查询B：多结果集的处理方式 #

EF Core 2.0本身也不支持直接用FromSql处理多结果集，但我们可以通过底层的ADO.NET连接来手动读取多个结果集，然后关联到Customer对象上。这种方式的优势是没有数据冗余（每个Customer只在第一个结果集出现一次，商品在第二个结果集单独列出），性能更优。

具体实现步骤： #

using (var command = dbContext.Database.GetDbConnection().CreateCommand())
{
    // 设置存储过程命令和参数
    command.CommandText = "EXEC dbo.[GetCustomerInfo] @TokenDtime=@TokenDtime";
    var tokenParam = command.CreateParameter();
    tokenParam.ParameterName = "@TokenDtime";
    tokenParam.Value = tokenDtime;
    command.Parameters.Add(tokenParam);

    // 打开连接（EF Core会自动管理连接状态，这里手动打开是为了确保读取结果集）
    dbContext.Database.OpenConnection();
    
    using (var reader = command.ExecuteReader())
    {
        // 第一步：读取第一个结果集，构建Customer和关联的Address对象
        var customerDict = new Dictionary<int, Customer>();
        while (reader.Read())
        {
            var customerId = reader.GetInt32(reader.GetOrdinal("CustomerId"));
            if (!customerDict.TryGetValue(customerId, out var customer))
            {
                customer = new Customer
                {
                    Id = customerId,
                    FirstName = reader.GetString(reader.GetOrdinal("CustomerFirstName")),
                    LastName = reader.GetString(reader.GetOrdinal("CustomerLastName")),
                    ShippingAddress = new Address
                    {
                        Id = reader.GetInt32(reader.GetOrdinal("CustomerShippingAddressId")),
                        Street1 = reader.GetString(reader.GetOrdinal("CustomerShippingAddressStreet1")),
                        Street2 = reader.GetString(reader.GetOrdinal("CustomerShippingAddressStreet2")),
                        City = reader.GetString(reader.GetOrdinal("CustomerShippingAddressCity")),
                        PostalCode = reader.GetString(reader.GetOrdinal("CustomerShippingAddressPostalCode")),
                        Country = reader.GetString(reader.GetOrdinal("CustomerShippingAddressCountry"))
                    },
                    BillingAddress = new Address
                    {
                        Id = reader.GetInt32(reader.GetOrdinal("CustomerBillingAddressId")),
                        Street1 = reader.GetString(reader.GetOrdinal("CustomerBillingAddressStreet1")),
                        Street2 = reader.GetString(reader.GetOrdinal("CustomerBillingAddressStreet2")),
                        City = reader.GetString(reader.GetOrdinal("CustomerBillingAddressCity")),
                        PostalCode = reader.GetString(reader.GetOrdinal("CustomerBillingAddressPostalCode")),
                        Country = reader.GetString(reader.GetOrdinal("CustomerBillingAddressCountry"))
                    },
                    Products = new List<Product>()
                };
                customerDict.Add(customerId, customer);
            }
        }

        // 第二步：切换到第二个结果集，读取商品并关联到对应的Customer
        reader.NextResult();
        while (reader.Read())
        {
            var customerId = reader.GetInt32(reader.GetOrdinal("CustomerId"));
            if (customerDict.TryGetValue(customerId, out var customer))
            {
                var product = new Product
                {
                    Id = reader.GetInt32(reader.GetOrdinal("ProductId")),
                    Name = reader.GetString(reader.GetOrdinal("ProductName")),
                    Price = reader.GetDecimal(reader.GetOrdinal("ProductPrice"))
                };
                customer.Products.Add(product);
            }
        }

        // 转换为列表返回
        return customerDict.Values.ToList();
    }
}

总结 #

• 查询A（单结果集）：实现简单但有数据冗余，需要手动分组去重和映射，适合关联关系简单的场景；
• 查询B（多结果集）：数据更高效无冗余，但需要手动处理多结果集的读取和关联，代码量稍多；
• 如果可以升级到EF Core 3.0+（对应ASP.NET Core 3.0+），多结果集的处理会更简洁，EF Core 3.0+支持直接用FromSqlRaw配合NextResult来读取多个结果集并映射到实体。

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