跳至主要內容

Dapper 多映射

SunnyFan大约 16 分钟约 4946 字

Dapper 多映射

简介

Dapper 的 Multi Mapping 功能允许将一条 SQL JOIN 查询的结果自动拆分并映射到多个 C# 对象。这对于一对多、多对一关联查询非常实用，避免了 N+1 查询问题。掌握 splitOn 参数、自定义类型处理器和一对多映射技巧，是高效使用 Dapper 的关键。

Dapper 本身是一个轻量级 Micro-ORM，它不提供完整的 ORM 功能（如变更跟踪、延迟加载），但提供了灵活的映射机制。Multi Mapping 是 Dapper 最核心的高级特性之一，它让开发者可以在享受原生 SQL 控制力的同时，避免手动编写繁琐的 DataReader 映射代码。

特点

核心概念

splitOn 参数详解

splitOn 是 Dapper Multi Mapping 的核心参数：
- 作用：告诉 Dapper 从哪一列开始，将结果行拆分到下一个类型
- 默认值：如果未指定，Dapper 默认寻找名为 "Id" 的列
- 格式：多个拆分点用逗号分隔，如 "CustomerId,ItemId"
- 规则：splitOn 指定的列会成为下一个类型的主键列

映射流程：
SELECT A.Id, A.Name, B.CustomerId, B.Email
        ^^^^^^^^^^^^  ^^^^^^^^^^^^^^^^
        类型 A 的列    类型 B 的列（从 CustomerId 开始拆分）
splitOn: "CustomerId"

映射规则

1. Dapper 按顺序将列映射到泛型参数指定的类型
2. 遇到 splitOn 指定的列时，开始映射下一个类型
3. 如果某列在当前类型中找不到对应属性，会尝试映射到后续类型
4. splitOn 列本身属于下一个类型（不是当前类型）
5. 列名不区分大小写（默认配置下）

实现

多表 JOIN 一对一映射

基础示例

// 定义实体类
public class Order
{
    public int OrderId { get; set; }
    public DateTime OrderDate { get; set; }
    public decimal TotalAmount { get; set; }
    public string Status { get; set; } = string.Empty;
    public int CustomerId { get; set; }
    public Customer Customer { get; set; }  // 导航属性
}

public class Customer
{
    public int CustomerId { get; set; }
    public string Name { get; set; } = string.Empty;
    public string Email { get; set; } = string.Empty;
    public string Phone { get; set; } = string.Empty;
}

// 一对一映射：Order -> Customer
var sql = @"
    SELECT o.OrderId, o.OrderDate, o.TotalAmount, o.Status,
           c.CustomerId, c.Name, c.Email, c.Phone
    FROM Orders o
    INNER JOIN Customers c ON o.CustomerId = c.CustomerId
    WHERE o.OrderDate >= @startDate";

var orders = connection.Query<Order, Customer, Order>(
    sql,
    (order, customer) =>
    {
        order.Customer = customer;  // 手动组装关联关系
        return order;
    },
    param: new { startDate = DateTime.Today.AddDays(-30) },
    splitOn: "CustomerId"   // 从 CustomerId 列开始拆分为 Customer 对象
).ToList();

列别名映射

// 当数据库列名与 C# 属性名不一致时，使用列别名
var sql = @"
    SELECT
        o.order_id     AS OrderId,
        o.order_date   AS OrderDate,
        o.total_amount AS TotalAmount,
        o.status       AS Status,
        c.customer_id  AS CustomerId,
        c.name         AS Name,
        c.email        AS Email
    FROM orders o
    INNER JOIN customers c ON o.customer_id = c.customer_id
    WHERE o.order_date >= @startDate";

var orders = connection.Query<Order, Customer, Order>(
    sql,
    (order, customer) =>
    {
        order.Customer = customer;
        return order;
    },
    new { startDate = DateTime.Today.AddDays(-30) },
    splitOn: "CustomerId"
).ToList();

使用 Dapper 的 Column Attribute

// 使用 [Column] 属性指定列名映射
using Dapper;

public class Order
{
    [Column("order_id")]
    public int OrderId { get; set; }

    [Column("order_date")]
    public DateTime OrderDate { get; set; }

    [Column("total_amount")]
    public decimal TotalAmount { get; set; }

    [Column("status")]
    public string Status { get; set; } = string.Empty;

    public Customer Customer { get; set; }
}

一对多集合映射

基础一对多

public class OrderWithItems
{
    public int OrderId { get; set; }
    public DateTime OrderDate { get; set; }
    public decimal TotalAmount { get; set; }
    public List<OrderItem> Items { get; set; } = new();
}

public class OrderItem
{
    public int ItemId { get; set; }
    public string ProductName { get; set; } = string.Empty;
    public int Quantity { get; set; }
    public decimal UnitPrice { get; set; }
}

// 关键：用 Dictionary 去重实现一对多
var sql = @"
    SELECT o.OrderId, o.OrderDate, o.TotalAmount,
           i.ItemId, i.ProductName, i.Quantity, i.UnitPrice
    FROM Orders o
    LEFT JOIN OrderItems i ON o.OrderId = i.OrderId
    WHERE o.CustomerId = @custId
    ORDER BY o.OrderId, i.ItemId";

var orderDict = new Dictionary<int, OrderWithItems>();

var result = connection.Query<OrderWithItems, OrderItem, OrderWithItems>(
    sql,
    (order, item) =>
    {
        // 如果该订单尚未在字典中，添加到字典
        if (!orderDict.TryGetValue(order.OrderId, out var existing))
        {
            existing = order;
            orderDict[order.OrderId] = existing;
        }
        // 如果订单项不为 null，添加到订单的 Items 集合
        if (item != null)
        {
            existing.Items.Add(item);
        }
        return existing;
    },
    param: new { custId = 1001 },
    splitOn: "ItemId"
).Distinct().ToList();  // Distinct 消除重复

三层映射：Order -> Customer + Items

public class OrderDetail
{
    public int OrderId { get; set; }
    public DateTime OrderDate { get; set; }
    public decimal TotalAmount { get; set; }
    public Customer Customer { get; set; }
    public List<OrderItem> Items { get; set; } = new();
}

// 三层映射需要两个 splitOn 点
var sql = @"
    SELECT o.OrderId, o.OrderDate, o.TotalAmount,
           c.CustomerId, c.Name, c.Email,
           i.ItemId, i.ProductName, i.Quantity, i.UnitPrice
    FROM Orders o
    INNER JOIN Customers c ON o.CustomerId = c.CustomerId
    LEFT JOIN OrderItems i ON o.OrderId = i.OrderId
    WHERE o.OrderId = @orderId
    ORDER BY i.ItemId";

var orderDict = new Dictionary<int, OrderDetail>();

var result = connection.Query<OrderDetail, Customer, OrderItem, OrderDetail>(
    sql,
    (order, customer, item) =>
    {
        if (!orderDict.TryGetValue(order.OrderId, out var existing))
        {
            existing = order;
            existing.Customer = customer;
            orderDict[order.OrderId] = existing;
        }
        if (item != null)
        {
            existing.Items.Add(item);
        }
        return existing;
    },
    new { orderId = 5001 },
    splitOn: "CustomerId,ItemId"  // 多个拆分点用逗号分隔
).Distinct().ToList();

四层及以上映射

// 四层映射：Order -> Customer -> Address + Items -> Product
var sql = @"
    SELECT o.OrderId, o.OrderDate, o.TotalAmount,
           c.CustomerId, c.Name, c.Email,
           a.AddressId, a.City, a.Province, a.Detail,
           i.ItemId, i.ProductName, i.Quantity,
           p.ProductId, p.Category, p.Brand
    FROM Orders o
    INNER JOIN Customers c ON o.CustomerId = c.CustomerId
    LEFT JOIN Addresses a ON c.DefaultAddressId = a.AddressId
    LEFT JOIN OrderItems i ON o.OrderId = i.OrderId
    LEFT JOIN Products p ON i.ProductId = p.ProductId
    WHERE o.OrderId = @orderId";

var orderDict = new Dictionary<int, OrderFull>();
var productDict = new Dictionary<int, Product>();

var result = connection.Query<OrderFull, Customer, Address, OrderItem, Product, OrderFull>(
    sql,
    (order, customer, address, item, product) =>
    {
        if (!orderDict.TryGetValue(order.OrderId, out var existing))
        {
            existing = order;
            customer.Address = address;
            existing.Customer = customer;
            orderDict[order.OrderId] = existing;
        }
        if (item != null && product != null)
        {
            if (!productDict.TryGetValue(product.ProductId, out var existingProduct))
            {
                existingProduct = product;
                productDict[product.ProductId] = existingProduct;
            }
            item.Product = existingProduct;
            existing.Items.Add(item);
        }
        return existing;
    },
    new { orderId = 5001 },
    splitOn: "CustomerId,AddressId,ItemId,ProductId"
).Distinct().ToList();

一对多映射的通用封装

/// <summary>
/// 通用一对多映射扩展方法
/// </summary>
public static class DapperMultiMappingExtensions
{
    /// <summary>
    /// 一对多映射：将查询结果映射为父实体列表，每个父实体包含子实体集合
    /// </summary>
    public static IEnumerable<TParent> MapOneToMany<TParent, TChild, TKey>(
        this IEnumerable<(TParent Parent, TChild Child)> source,
        Func<TParent, TKey> parentKeySelector,
        Func<TChild, TKey> childForeignKeySelector,
        Action<TParent, TChild> addChild)
    {
        var dict = new Dictionary<TKey, TParent>();

        foreach (var (parent, child) in source)
        {
            var key = parentKeySelector(parent);
            if (!dict.TryGetValue(key, out var existing))
            {
                dict[key] = parent;
                existing = parent;
            }
            if (child != null)
            {
                addChild(existing, child);
            }
        }

        return dict.Values;
    }
}

// 使用扩展方法简化一对多映射
var sql = @"
    SELECT o.OrderId, o.OrderDate, o.TotalAmount,
           i.ItemId, i.ProductName, i.Quantity, i.UnitPrice
    FROM Orders o
    LEFT JOIN OrderItems i ON o.OrderId = i.OrderId
    WHERE o.CustomerId = @custId";

var rows = connection.Query<OrderWithItems, OrderItem, (OrderWithItems, OrderItem)>(
    sql,
    (order, item) => (order, item),
    new { custId = 1001 },
    splitOn: "ItemId"
);

var result = rows.MapOneToMany(
    parentKeySelector: x => x.Parent.OrderId,
    childForeignKeySelector: x => x.Child?.ItemId ?? -1,
    addChild: (parent, child) => parent.Items.Add(child)
).ToList();

自定义类型处理器

JSON 类型处理器

using System.Data;
using System.Text.Json;
using Dapper;

/// <summary>
/// JSON 类型处理器 — 将 C# 对象序列化为 JSON 字符串存储
/// 适用于 PostgreSQL 的 JSONB、SQL Server 的 NVARCHAR(MAX) 等
/// </summary>
public class JsonTypeHandler<T> : ITypeHandler
{
    private readonly JsonSerializerOptions _options = new()
    {
        PropertyNamingPolicy = JsonNamingPolicy.CamelCase,
        WriteIndented = false
    };

    public void SetValue(IDbDataParameter parameter, object? value)
    {
        parameter.Value = value == null
            ? DBNull.Value
            : JsonSerializer.Serialize(value, typeof(T), _options);
        parameter.DbType = DbType.String;
    }

    public T? Parse(Type destinationType, object value)
    {
        return value == null || value == DBNull.Value
            ? default
            : JsonSerializer.Deserialize<T>((string)value, _options);
    }

    public Type HandlerType => typeof(T);
}

// 注册处理器（只需注册一次，通常在应用启动时）
SqlMapper.AddTypeHandler(new JsonTypeHandler<Address>());
SqlMapper.AddTypeHandler(new JsonTypeHandler<List<string>>());
SqlMapper.AddTypeHandler(new JsonTypeHandler<Dictionary<string, object>>());

// 使用示例
public class Customer
{
    public int CustomerId { get; set; }
    public string Name { get; set; } = string.Empty;

    // 自动序列化为 JSON 存储
    [JsonElementType(typeof(Address))]
    public Address ShippingAddress { get; set; } = new();

    [JsonElementType(typeof(List<string>))]
    public List<string> Tags { get; set; } = new();
}

public class Address
{
    public string Province { get; set; } = string.Empty;
    public string City { get; set; } = string.Empty;
    public string Detail { get; set; } = string.Empty;
    public string ZipCode { get; set; } = string.Empty;
}

枚举类型处理器

/// <summary>
/// 枚举值处理器 — 将枚举存储为字符串而非整数
/// </summary>
public class EnumAsStringTypeHandler<T> : ITypeHandler
    where T : struct, Enum
{
    public void SetValue(IDbDataParameter parameter, object? value)
    {
        parameter.Value = value == null
            ? DBNull.Value
            : value.ToString();
        parameter.DbType = DbType.String;
    }

    public T Parse(Type destinationType, object value)
    {
        return Enum.Parse<T>((string)value);
    }
}

// 注册
SqlMapper.AddTypeHandler(new EnumAsStringTypeHandler<OrderStatus>());
SqlMapper.AddTypeHandler(new EnumAsStringTypeHandler<PaymentMethod>());

// 枚举定义
public enum OrderStatus
{
    Pending,
    Processing,
    Shipped,
    Delivered,
    Cancelled
}

// 数据库中存储为字符串 'Pending', 'Processing' 等
// 而非整数 0, 1, 2...

PostgreSQL 特殊类型处理器

/// <summary>
/// PostgreSQL 数组类型处理器
/// </summary>
public class PgIntArrayHandler : ITypeHandler
{
    public void SetValue(IDbDataParameter parameter, object? value)
    {
        if (value is int[] arr)
        {
            parameter.Value = string.Join(",", arr);
            parameter.DbType = DbType.String;
        }
        else
        {
            parameter.Value = DBNull.Value;
        }
    }

    public int[] Parse(Type destinationType, object value)
    {
        if (value is string str && !string.IsNullOrEmpty(str))
        {
            return str.Split(',', StringSplitOptions.RemoveEmptyEntries)
                .Select(int.Parse)
                .ToArray();
        }
        return Array.Empty<int>();
    }
}

/// <summary>
/// PostgreSQL Hstore 类型处理器（键值对）
/// </summary>
public class PgHstoreHandler : ITypeHandler
{
    public void SetValue(IDbDataParameter parameter, object? value)
    {
        if (value is Dictionary<string, string> dict)
        {
            parameter.Value = string.Join(",",
                dict.Select(kv => $"{kv.Key}=>{kv.Value}"));
        }
        else
        {
            parameter.Value = DBNull.Value;
        }
    }

    public Dictionary<string, string> Parse(Type destinationType, object value)
    {
        // 解析 "key1=>value1,key2=>value2" 格式
        var result = new Dictionary<string, string>();
        if (value is string str)
        {
            foreach (var pair in str.Split(','))
            {
                var parts = pair.Split("=>", 2);
                if (parts.Length == 2)
                {
                    result[parts[0]] = parts[1];
                }
            }
        }
        return result;
    }
}

QueryMultiple 多结果集

基础用法

// 一次请求读取多个结果集，减少网络往返
var sql = @"
    SELECT * FROM Orders WHERE OrderId = @id;
    SELECT * FROM OrderItems WHERE OrderId = @id;
    SELECT * FROM OrderLogs WHERE OrderId = @id;";

using var multi = connection.QueryMultiple(sql, new { id = 5001 });

// 按顺序读取结果集
var order = multi.ReadFirstOrDefault<Order>();
var items = multi.Read<OrderItem>().ToList();
var logs  = multi.Read<OrderLog>().ToList();

// 组装结果
if (order != null)
{
    order.Items = items;
    order.Logs = logs;
}

仪表板多维度数据

// 一次查询获取仪表板需要的所有数据
public async Task<DashboardDto> GetDashboardAsync(DateTime date)
{
    var sql = @"
        -- 总体统计
        SELECT
            COUNT(*) AS TotalOrders,
            SUM(CASE WHEN Status = 'completed' THEN 1 ELSE 0 END) AS CompletedOrders,
            SUM(Amount) AS TotalRevenue,
            AVG(Amount) AS AvgOrderValue
        FROM Orders
        WHERE CAST(CreatedAt AS DATE) = @date;

        -- 按状态分组
        SELECT Status, COUNT(*) AS Count
        FROM Orders
        WHERE CAST(CreatedAt AS DATE) = @date
        GROUP BY Status;

        -- Top 10 商品
        SELECT TOP 10
            ProductName,
            SUM(Quantity) AS TotalQty,
            SUM(Quantity * UnitPrice) AS Revenue
        FROM OrderItems oi
        INNER JOIN Orders o ON oi.OrderId = o.OrderId
        WHERE CAST(o.CreatedAt AS DATE) = @date
        GROUP BY ProductName
        ORDER BY Revenue DESC;";

    using var multi = await connection.QueryMultipleAsync(sql, new { date });

    var overview = await multi.ReadFirstOrDefaultAsync<OverviewDto>();
    var byStatus = (await multi.ReadAsync<StatusCountDto>()).ToList();
    var topProducts = (await multi.ReadAsync<ProductRankDto>()).ToList();

    return new DashboardDto
    {
        Overview = overview,
        ByStatus = byStatus,
        TopProducts = topProducts
    };
}

public class DashboardDto
{
    public OverviewDto? Overview { get; set; }
    public List<StatusCountDto> ByStatus { get; set; } = new();
    public List<ProductRankDto> TopProducts { get; set; } = new();
}

GridReader 与事务配合

// QueryMultiple 配合事务使用
public async Task<OrderDetailDto> GetOrderDetailAsync(int orderId)
{
    using var transaction = connection.BeginTransaction();

    try
    {
        var sql = @"
            SELECT * FROM Orders WHERE OrderId = @orderId;
            SELECT * FROM OrderItems WHERE OrderId = @orderId;
            SELECT * FROM Payments WHERE OrderId = @orderId;
            SELECT * FROM Shipments WHERE OrderId = @orderId;";

        using var multi = await connection.QueryMultipleAsync(
            sql, new { orderId }, transaction);

        var order = await multi.ReadFirstOrDefaultAsync<Order>();
        var items = (await multi.ReadAsync<OrderItem>()).ToList();
        var payments = (await multi.ReadAsync<Payment>()).ToList();
        var shipments = (await multi.ReadAsync<Shipment>()).ToList();

        transaction.Commit();

        return new OrderDetailDto
        {
            Order = order,
            Items = items,
            Payments = payments,
            Shipments = shipments
        };
    }
    catch
    {
        transaction.Rollback();
        throw;
    }
}

实战模式

模式一：仓储层封装

/// <summary>
/// 订单仓储 — 封装 Dapper Multi Mapping
/// </summary>
public class OrderRepository
{
    private readonly IDbConnection _connection;

    public OrderRepository(IDbConnection connection)
    {
        _connection = connection;
    }

    /// <summary>
    /// 获取订单详情（含客户、订单项、商品）
    /// </summary>
    public async Task<OrderDetail?> GetOrderDetailAsync(int orderId)
    {
        var sql = @"
            SELECT
                o.OrderId, o.OrderDate, o.TotalAmount, o.Status,
                c.CustomerId, c.Name, c.Email, c.Phone,
                i.ItemId, i.ProductName, i.Quantity, i.UnitPrice,
                p.ProductId, p.Category, p.Brand, p.ImageUrl
            FROM Orders o
            INNER JOIN Customers c ON o.CustomerId = c.CustomerId
            LEFT JOIN OrderItems i ON o.OrderId = i.OrderId
            LEFT JOIN Products p ON i.ProductId = p.ProductId
            WHERE o.OrderId = @orderId
            ORDER BY i.ItemId";

        var orderDict = new Dictionary<int, OrderDetail>();
        var productDict = new Dictionary<int, ProductInfo>();

        var result = await _connection.QueryAsync<OrderDetail, CustomerInfo, OrderItemInfo, ProductInfo, OrderDetail>(
            sql,
            (order, customer, item, product) =>
            {
                if (!orderDict.TryGetValue(order.OrderId, out var existing))
                {
                    existing = order;
                    existing.Customer = customer;
                    orderDict[order.OrderId] = existing;
                }
                if (item != null)
                {
                    if (product != null)
                    {
                        if (!productDict.TryGetValue(product.ProductId, out var existingProduct))
                        {
                            existingProduct = product;
                            productDict[product.ProductId] = existingProduct;
                        }
                        item.Product = existingProduct;
                    }
                    existing.Items.Add(item);
                }
                return existing;
            },
            new { orderId },
            splitOn: "CustomerId,ItemId,ProductId"
        );

        return result.Distinct().FirstOrDefault();
    }

    /// <summary>
    /// 获取订单列表（含客户信息）
    /// </summary>
    public async Task<List<OrderListItem>> GetOrderListAsync(
        int customerId, int page, int pageSize)
    {
        var sql = @"
            SELECT
                o.OrderId, o.OrderDate, o.TotalAmount, o.Status,
                c.CustomerId, c.Name, c.Email
            FROM Orders o
            INNER JOIN Customers c ON o.CustomerId = c.CustomerId
            WHERE o.CustomerId = @customerId
            ORDER BY o.OrderDate DESC
            OFFSET @offset ROWS FETCH NEXT @pageSize ROWS ONLY";

        return (await _connection.QueryAsync<OrderListItem, CustomerInfo, OrderListItem>(
            sql,
            (order, customer) =>
            {
                order.Customer = customer;
                return order;
            },
            new
            {
                customerId,
                offset = (page - 1) * pageSize,
                pageSize
            },
            splitOn: "CustomerId"
        )).ToList();
    }
}

模式二：动态查询构建器

/// <summary>
/// 通用多映射查询构建器
/// </summary>
public class MultiMappingQueryBuilder
{
    private readonly List<string> _selectColumns = new();
    private readonly List<string> _joinClauses = new();
    private readonly List<string> _whereClauses = new();
    private readonly List<string> _orderByClauses = new();
    private readonly Dictionary<string, object> _parameters = new();

    public MultiMappingQueryBuilder Select(params string[] columns)
    {
        _selectColumns.AddRange(columns);
        return this;
    }

    public MultiMappingQueryBuilder Join(
        string table, string alias, string onClause)
    {
        _joinClauses.Add($"INNER JOIN {table} {alias} ON {onClause}");
        return this;
    }

    public MultiMappingQueryBuilder LeftJoin(
        string table, string alias, string onClause)
    {
        _joinClauses.Add($"LEFT JOIN {table} {alias} ON {onClause}");
        return this;
    }

    public MultiMappingQueryBuilder Where(string clause, object? param = null)
    {
        _whereClauses.Add(clause);
        if (param != null)
        {
            _parameters.Add($"p{_parameters.Count}", param);
        }
        return this;
    }

    public MultiMappingQueryBuilder OrderBy(string clause)
    {
        _orderByClauses.Add(clause);
        return this;
    }

    public string BuildSql()
    {
        var sb = new StringBuilder();
        sb.AppendLine($"SELECT {string.Join(", ", _selectColumns)}");
        sb.AppendLine("FROM Orders o");

        foreach (var join in _joinClauses)
        {
            sb.AppendLine(join);
        }

        if (_whereClauses.Count > 0)
        {
            sb.AppendLine($"WHERE {string.Join(" AND ", _whereClauses)}");
        }

        if (_orderByClauses.Count > 0)
        {
            sb.AppendLine($"ORDER BY {string.Join(", ", _orderByClauses)}");
        }

        return sb.ToString();
    }

    public DynamicParameters BuildParameters()
    {
        var dp = new DynamicParameters();
        foreach (var (key, value) in _parameters)
        {
            dp.Add(key, value);
        }
        return dp;
    }
}

// 使用构建器
public class OrderQueryService
{
    private readonly IDbConnection _connection;

    public async Task<List<OrderDetail>> SearchOrdersAsync(
        int? customerId, string? status, DateTime? startDate, int page, int pageSize)
    {
        var builder = new MultiMappingQueryBuilder()
            .Select("o.OrderId", "o.OrderDate", "o.TotalAmount", "o.Status",
                    "c.CustomerId", "c.Name", "c.Email",
                    "i.ItemId", "i.ProductName", "i.Quantity")
            .Join("Customers", "c", "o.CustomerId = c.CustomerId")
            .LeftJoin("OrderItems", "i", "o.OrderId = i.OrderId")
            .OrderBy("o.OrderDate DESC");

        if (customerId.HasValue)
            builder.Where("o.CustomerId = @customerId", customerId.Value);

        if (!string.IsNullOrEmpty(status))
            builder.Where("o.Status = @status", status);

        if (startDate.HasValue)
            builder.Where("o.OrderDate >= @startDate", startDate.Value);

        builder.Where("o.OrderId > @lastId", (page - 1) * pageSize);
        builder.Where("o.OrderId <= @topId", page * pageSize);

        var sql = builder.BuildSql();
        var param = builder.BuildParameters();

        var orderDict = new Dictionary<int, OrderDetail>();

        return (await _connection.QueryAsync<OrderDetail, CustomerInfo, OrderItemInfo, OrderDetail>(
            sql,
            (order, customer, item) =>
            {
                if (!orderDict.TryGetValue(order.OrderId, out var existing))
                {
                    existing = order;
                    existing.Customer = customer;
                    orderDict[order.OrderId] = existing;
                }
                if (item != null)
                {
                    existing.Items.Add(item);
                }
                return existing;
            },
            param,
            splitOn: "CustomerId,ItemId"
        )).Distinct().ToList();
    }
}

模式三：异步批量操作

/// <summary>
/// 批量操作服务 — 使用 Dapper 的高效批量查询
/// </summary>
public class BatchQueryService
{
    private readonly IDbConnection _connection;

    /// <summary>
    /// 根据 ID 列表批量查询订单（含客户信息）
    /// </summary>
    public async Task<List<OrderDetail>> GetOrdersByIdsAsync(IEnumerable<int> orderIds)
    {
        var idList = orderIds.ToList();
        if (idList.Count == 0) return new List<OrderDetail>();

        // Dapper 原生支持 IN 查询
        var sql = @"
            SELECT
                o.OrderId, o.OrderDate, o.TotalAmount, o.Status,
                c.CustomerId, c.Name, c.Email
            FROM Orders o
            INNER JOIN Customers c ON o.CustomerId = c.CustomerId
            WHERE o.OrderId IN @orderIds";

        return (await _connection.QueryAsync<OrderDetail, CustomerInfo, OrderDetail>(
            sql,
            (order, customer) =>
            {
                order.Customer = customer;
                return order;
            },
            new { orderIds = idList },
            splitOn: "CustomerId"
        )).ToList();
    }

    /// <summary>
    /// 使用 QueryMultiple 批量查询多种数据
    /// </summary>
    public async Task<BatchResult> GetBatchDataAsync(
        List<int> orderIds, List<int> customerIds)
    {
        var sql = @"
            SELECT * FROM Orders WHERE OrderId IN @orderIds;
            SELECT * FROM Customers WHERE CustomerId IN @customerIds;
            SELECT * FROM OrderItems WHERE OrderId IN @orderIds;";

        using var multi = await _connection.QueryMultipleAsync(sql,
            new { orderIds, customerIds });

        var orders = (await multi.ReadAsync<Order>()).ToList();
        var customers = (await multi.ReadAsync<Customer>()).ToList();
        var items = (await multi.ReadAsync<OrderItem>()).ToList();

        return new BatchResult
        {
            Orders = orders,
            Customers = customers,
            Items = items
        };
    }
}

public class BatchResult
{
    public List<Order> Orders { get; set; } = new();
    public List<Customer> Customers { get; set; } = new();
    public List<OrderItem> Items { get; set; } = new();
}

性能优化

避免笛卡尔积

// 错误：一对多 + 一对多导致笛卡尔积
// 订单 -> 多个商品项 -> 每个商品项有多个标签
// 如果订单有 3 个商品项，每个商品项有 4 个标签
// 结果行数 = 3 * 4 = 12 行（而非 3 + 4 = 7 行）

// 正确方案：拆分为多次查询
public async Task<OrderWithItemsAndTags> GetOrderFullAsync(int orderId)
{
    // 查询 1：订单 + 客户
    var orderSql = @"
        SELECT o.*, c.*
        FROM Orders o
        JOIN Customers c ON o.CustomerId = c.CustomerId
        WHERE o.OrderId = @orderId";

    var order = await _connection.QueryFirstOrDefaultAsync<OrderWithCustomer>(
        orderSql, new { orderId },
        splitOn: "CustomerId");

    if (order == null) return null!;

    // 查询 2：订单项 + 商品
    var itemsSql = @"
        SELECT i.*, p.*
        FROM OrderItems i
        LEFT JOIN Products p ON i.ProductId = p.ProductId
        WHERE i.OrderId = @orderId";

    order.Items = (await _connection.QueryAsync<OrderItemWithProduct>(
        itemsSql, new { orderId },
        splitOn: "ProductId")).ToList();

    return new OrderWithItemsAndTags
    {
        Order = order,
        Items = order.Items
    };
}

缓存映射结果

/// <summary>
/// 带缓存的 Dapper 查询服务
/// </summary>
public class CachedOrderService
{
    private readonly IDbConnection _connection;
    private readonly IMemoryCache _cache;

    public async Task<Customer> GetCustomerAsync(int customerId)
    {
        var cacheKey = $"customer:{customerId}";
        return await _cache.GetOrCreateAsync(cacheKey, async entry =>
        {
            entry.AbsoluteExpirationRelativeToNow = TimeSpan.FromMinutes(10);

            var sql = @"
                SELECT c.*, a.*
                FROM Customers c
                LEFT JOIN Addresses a ON c.DefaultAddressId = a.AddressId
                WHERE c.CustomerId = @customerId";

            return await _connection.QueryFirstOrDefaultAsync<Customer>(
                sql, new { customerId },
                splitOn: "AddressId");
        });
    }
}

优点

缺点

总结

Dapper Multi Mapping 是解决关系型查询映射的高效工具。一对一场景用 splitOn 即可，一对多需配合 Dictionary 去重。自定义类型处理器扩展了 Dapper 对复杂类型的支持。在性能和灵活性之间，Multi Mapping 提供了比 EF Core 更轻量、比手写 DataReader 更安全的中间方案。对于嵌套层级较深的场景，建议拆分为多次查询或使用 QueryMultiple 分别处理。

关键知识点

  • splitOn 参数指定拆分边界列，默认值为 "Id"
  • 一对多映射必须用 Dictionary 做主键去重，防止父对象重复
  • QueryMultiple 适合读取多个独立结果集，减少数据库连接次数
  • ITypeHandler 可处理 JSON 列、枚举、自定义值类型等特殊映射
  • 笛卡尔积是一对多 + 一对多查询的主要风险，建议拆分查询
  • 列别名可以解决数据库列名与 C# 属性名不一致的问题
  • QueryMultiple 必须按 SQL 中 SELECT 的顺序读取结果集

项目落地视角

  • 所有 Multi Mapping 查询必须覆盖单元测试，验证对象关系正确性
  • 复杂映射逻辑封装为扩展方法，避免业务代码中重复 lambda
  • LEFT JOIN 场景注意 null 检查，避免空引用异常
  • 监控慢查询，JOIN 超过 3 张表时考虑拆分或使用视图
  • splitOn 列名使用常量定义，避免字符串硬编码
  • 一对多 + 一对多组合查询时，优先拆分为多次查询

常见误区

  • splitOn 列名写错，导致所有字段映射到同一个对象
  • 一对多不用 Dictionary 去重，导致父对象重复 N 条
  • 在 lambda 内部执行数据库查询，触发 N+1 问题
  • 忽略 LEFT JOIN 产生的 null 行，未做判空处理
  • 三层以上映射不封装，lambda 嵌套过深难以维护
  • 一对多 + 一对多组合查询产生笛卡尔积，结果数量远超预期
  • QueryMultiple 不按顺序读取结果集，导致数据错位
  • 忘记调用 Distinct()，导致一对多映射中父对象重复

进阶路线

  • 学习 Dapper Contrib 和 Dapper Builder 扩展库
  • 研究 Repository 模式下 Dapper 的封装最佳实践
  • 掌握 Slapper.AutoMapper 实现自动嵌套映射
  • 学习 Dapper 与 Unit of Work 模式的结合
  • 探索 Dapper + CQRS 架构在微服务中的应用
  • 研究批量查询的性能优化策略

适用场景

  • 报表查询需要关联多张表但不需要 ORM 跟踪
  • 微服务中轻量级数据访问层，追求极致查询性能
  • 旧系统改造，需要逐步替换手写 ADO.NET
  • 读多写少的 API 接口，查询复杂度高但变更频率低
  • 需要精确控制 SQL 的性能敏感场景
  • 与存储过程配合的数据访问层

落地建议

  • 将常用映射关系封装为静态扩展方法，统一维护
  • 使用事务包裹 QueryMultiple，保证多个结果集的一致性
  • 对 splitOn 列使用常量定义，避免字符串硬编码
  • 配合 MiniProfiler 或 APM 工具监控 SQL 执行时间
  • 为所有 Multi Mapping 查询编写单元测试
  • 一对多映射封装为通用方法，避免在每个查询中重复 Dictionary 逻辑
  • 笛卡尔积风险较高的场景优先拆分为多次查询

排错清单

  • 映射结果为空：检查 splitOn 列名是否与 SELECT 列名完全一致
  • 父对象重复：确认 Dictionary 去重逻辑是否正确
  • 字段值为 0 或 null：检查列名大小写与属性名的匹配
  • 类型转换异常：确认数据库列类型与 C# 属性类型兼容
  • QueryMultiple 数据错位：确认读取顺序与 SQL 中 SELECT 顺序一致
  • 笛卡尔积：检查是否存在一对多 + 一对多 JOIN 组合
  • LEFT JOIN 的 null 值：确认 lambda 中是否有 null 检查

复盘问题

  • 当前项目中是否有可以用 Multi Mapping 替代的 N+1 查询？
  • splitOn 参数是否集中在常量类中统一管理？
  • 一对多映射是否都有对应的单元测试覆盖？
  • 是否存在映射 lambda 过于复杂需要拆分的情况？
  • 是否有笛卡尔积风险的一对多 + 一对多查询？
  • 自定义类型处理器是否覆盖了所有需要的类型？
  • QueryMultiple 的使用场景是否合理？是否可以用批量查询替代？

延伸阅读

Copyright © 2026 SunnyFan