C#中使用Dapper实现SQL Server多表数据一次性映射到多层嵌套类的方法

核心思路 #

• 基于Dapper的QueryMultiple单次发起4条独立查询，仅1次数据库往返，无多表JOIN产生的冗余数据，服务端/客户端开销最低
• 内存中通过字典做关联映射，最终统一转换为ObservableCollection类型适配Caliburn Micro框架要求

完整实现代码 #

using Dapper;
using System.Collections.ObjectModel;
using System.Data.SqlClient;
using System.Linq;

public async Task<ObservableCollection<InvoiceModel>> GetInvoicesAsync(DateTime? startDate = null, DateTime? endDate = null)
{
    var connectionString = "你的SQL Server连接字符串";
    using var conn = new SqlConnection(connectionString);
    await conn.OpenAsync();

    // 1次请求返回4个独立结果集，用表变量复用过滤条件，避免重复写查询逻辑
    var sql = @"
DECLARE @FilterInvoiceIds TABLE (Id INT);
INSERT INTO @FilterInvoiceIds
SELECT Id FROM Invoices 
WHERE (@StartDate IS NULL OR CAST(DateCreated AS DATETIME) >= @StartDate)
  AND (@EndDate IS NULL OR CAST(DateCreated AS DATETIME) <= @EndDate);

-- 结果集1：发票+关联客户
SELECT i.*, c.* 
FROM Invoices i
INNER JOIN @FilterInvoiceIds fi ON i.Id = fi.Id
LEFT JOIN Customers c ON i.CustomerId = c.Id;

-- 结果集2：关联客户的手机号
SELECT p.*
FROM PhoneNumbers p
INNER JOIN Customers c ON p.CustomerId = c.Id
INNER JOIN Invoices i ON c.Id = i.CustomerId
INNER JOIN @FilterInvoiceIds fi ON i.Id = fi.Id;

-- 结果集3：发票明细
SELECT ii.*
FROM InvoiceItems ii
INNER JOIN @FilterInvoiceIds fi ON ii.InvoiceId = fi.Id;

-- 结果集4：发票支付记录
SELECT ip.*
FROM InvoicePayments ip
INNER JOIN @FilterInvoiceIds fi ON ip.InvoiceId = fi.Id;
";

    using var multi = await conn.QueryMultipleAsync(sql, new { StartDate = startDate, EndDate = endDate });

    // 映射第一个结果集，SplitOn参数填Customer表的主键字段名Id，按发票Id存字典
    var invoiceDict = (await multi.ReadAsync<InvoiceModel, CustomerModel, InvoiceModel>(
        (invoice, customer) =>
        {
            invoice.Customer = customer;
            // 初始化集合属性避免空引用
            invoice.Customer.PhoneNumbers = new ObservableCollection<PhoneNumberModel>();
            invoice.Items = new ObservableCollection<InvoiceItemModel>();
            invoice.Payments = new ObservableCollection<InvoicePaymentModel>();
            return invoice;
        },
        splitOn: "Id"))
    .ToDictionary(x => x.Id);

    // 映射第二个结果集：手机号按客户Id分组，塞到对应Customer属性
    var phoneNumbers = (await multi.ReadAsync<PhoneNumberModel>()).GroupBy(x => x.CustomerId);
    foreach (var group in phoneNumbers)
    {
        var relatedInvoices = invoiceDict.Values.Where(x => x.Customer.Id == group.Key);
        foreach (var invoice in relatedInvoices)
        {
            invoice.Customer.PhoneNumbers = new ObservableCollection<PhoneNumberModel>(group.ToList());
        }
    }

    // 映射第三个结果集：明细按发票Id分组
    var invoiceItems = (await multi.ReadAsync<InvoiceItemModel>()).GroupBy(x => x.InvoiceId);
    foreach (var group in invoiceItems)
    {
        if (invoiceDict.TryGetValue(group.Key, out var invoice))
        {
            invoice.Items = new ObservableCollection<InvoiceItemModel>(group.ToList());
        }
    }

    // 映射第四个结果集：支付记录按发票Id分组
    var invoicePayments = (await multi.ReadAsync<InvoicePaymentModel>()).GroupBy(x => x.InvoiceId);
    foreach (var group in invoicePayments)
    {
        if (invoiceDict.TryGetValue(group.Key, out var invoice))
        {
            invoice.Payments = new ObservableCollection<InvoicePaymentModel>(group.ToList());
        }
    }

    // 最终返回要求的集合类型
    return new ObservableCollection<InvoiceModel>(invoiceDict.Values);
}

注意事项 #

• 第一个结果集的SplitOn参数必须指定为Customer表的主键字段名Id，Dapper会按这个字段切割返回列，前半部分映射到InvoiceModel，后半部分映射到CustomerModel
• 如果不需要过滤发票可删除表变量逻辑，直接查全量数据即可
• 所有集合属性初始化时直接转成ObservableCollection，无需后续二次转换，适配Caliburn Micro的绑定要求

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