Dapper动态映射一对多嵌套实体时出现映射错误求助

我最近在开发一个动态获取一对多嵌套实体的功能，用Dapper做ORM的时候遇到了棘手的映射问题，SQL本身没问题，但实体映射完全不对，想请大家帮忙看看！

问题详情 #

• 我动态构建了查询嵌套实体的SQL，在pgAdmin中执行能正常返回2行数据（1条父记录+关联的1条子记录）
• 但用Dapper的QueryAsync多映射时，结果完全不符合预期：
  • map函数里的objs数组有2个元素：objs[0]是父实体，Id等属性值正常，但它的子集合始终是空的
  • objs[1]是子实体类型，但所有属性都是null，Id更是0
• 我确认生成的SQL是正确的，问题肯定出在Dapper的映射环节

关键代码 #

public async Task<object?> GetByIdAsync(DynamicGetByIdRequest getByIdReq, CancellationToken cancellationToken = default)
{
    Type rootType = ResolveEntityType(getByIdReq.DynamicGetParameter!.EntityName);
    var sqlMeta = _metaExtractor.Extract(rootType);
    var queryParts = BuildQuery(getByIdReq.DynamicGetParameter, sqlMeta, rootType, "T0");

    string sql = $@"SELECT {queryParts.SelectClause} FROM {sqlMeta.Table.Quote()} AS {"T0".Quote()} {queryParts.JoinClause} WHERE {"T0".Quote()}.{"Id".Quote()} = @id";

    var lookup = new Dictionary<int, object>();
    await _db.QueryAsync(
        sql,
        types: queryParts.Types.ToArray(),
        map: (object[] objs) =>
        {
            var root = objs[0];
            var rootId = (int)root.GetType().GetProperty("Id").GetValue(root);

            if (!lookup.TryGetValue(rootId, out object existing))
            {
                lookup.Add(rootId, root);
                existing = root;
                // Initialize collection properties
                foreach (var prop in root.GetType().GetProperties())
                {
                    if (prop.PropertyType.IsGenericType && prop.PropertyType.GetGenericTypeDefinition() == typeof(List<>))
                    {
                        prop.SetValue(existing, Activator.CreateInstance(prop.PropertyType));
                    }
                }
            }

            // Handle child objects
            for (int i = 1; i < objs.Length; i++)
            {
                var child = objs[i];
                if (child == null) continue;

                var childType = queryParts.Types[i];
                var navProp = rootType.GetProperties()
                    .FirstOrDefault(p => p.PropertyType.IsGenericType && p.PropertyType.GetGenericTypeDefinition() == typeof(List<>) && p.PropertyType.GetGenericArguments()[0] == childType);

                if (navProp != null)
                {
                    var collection = navProp.GetValue(existing) as IList;
                    var childId = (int)child.GetType().GetProperty("Id").GetValue(child);
                    if (!collection.Cast<object>().Any(x => (int)x.GetType().GetProperty("Id").GetValue(x) == childId))
                    {
                        collection.Add(child);
                    }
                }
            }

            return existing;
        },
        param: new { id = getByIdReq.Id },
        splitOn: string.Join(",", queryParts.SplitOnColumns)
    );

    return lookup.Values.FirstOrDefault();
}

private (List<Type> Types, string SelectClause, string JoinClause, List<string> SplitOnColumns) BuildQuery(DynamicGetParameter param, SqlMeta meta, Type entityType, string alias, string? parentAlias = null)
{
    var types = new List<Type> { entityType };
    var selectParts = new List<string>();
    var joinParts = new List<string>();
    var splitOnColumns = new List<string>();

    // 1. Select all scalar properties for root entity
    foreach (var prop in meta.ScalarProps)
    {
        if (param.Properties == null || param.Properties.Contains(prop.Name, StringComparer.OrdinalIgnoreCase))
        {
            selectParts.Add($"{alias.Quote()}.{prop.Name.Quote()} AS \"{prop.Name}\"");
        }
    }

    // Include root ID (only once)
    if (!selectParts.Any(sp => sp.Contains($"\"Id\"")))
    {
        selectParts.Add($"{alias.Quote()}.{"Id".Quote()} AS \"Id\"");
    }

    // 2. Handle nested properties
    if (param.Navigation != null)
    {
        foreach (var nav in param.Navigation)
        {
            Type navType = ResolveEntityType(nav.EntityName);
            var navMeta = _metaExtractor.Extract(navType);
            string navAlias = $"{alias}_{nav.EntityName}";
            types.Add(navType);

            // Join clause
            joinParts.Add($"LEFT JOIN {navMeta.Table.Quote()} AS {navAlias.Quote()} " +
                         $"ON {alias.Quote()}.{"Id".Quote()} = {navAlias.Quote()}.{"ReferralId".Quote()}");

            // Select child properties
            foreach (var prop in navMeta.ScalarProps)
            {
                if (nav.Properties == null || nav.Properties.Contains(prop.Name, StringComparer.OrdinalIgnoreCase))
                {
                    selectParts.Add($"{navAlias.Quote()}.{prop.Name.Quote()} AS \"{prop.Name}\"");
                }
            }

            // Select child ID again with unique alias for splitOn
            selectParts.Add($"{navAlias.Quote()}.{"Id".Quote()} AS \"{navAlias}_Id\"");
            splitOnColumns.Add($"{navAlias}_Id");
        }
    }

    return (
        Types: types,
        SelectClause: string.Join(", ", selectParts),
        JoinClause: string.Join(" ", joinParts),
        SplitOnColumns: splitOnColumns
    );
}

生成的SQL（示例） #

SELECT "r"."Id", "r"."ReservationStatus", "r"."Language", "r"."ReferralSource", "ra"."Id" AS "ra_Id", "ra"."Name", "ra"."Email"
FROM "Reservation" AS "r" LEFT JOIN "ReservationAddress" AS "ra" ON "r"."Id" = "ra"."ReferralId"
WHERE "r"."Id" = @id

问题分析与解决方案 #

我仔细排查了代码，发现两个核心问题导致了映射失败：

1. 父子实体属性名冲突，Dapper无法区分

你的子实体属性直接用AS "{prop.Name}"，如果父实体和子实体有同名属性（比如Id、Name），Dapper会把同一个列值同时赋值给父和子的对应属性，导致子实体的真实属性值被覆盖，甚至因为列顺序问题，子实体无法获取到正确的值，最终变成null/0。

修复方案：给子实体的所有属性加上唯一前缀（比如导航别名），确保列名和父实体不重复：

// 修改子实体属性的select语句
foreach (var prop in navMeta.ScalarProps)
{
    if (nav.Properties == null || nav.Properties.Contains(prop.Name, StringComparer.OrdinalIgnoreCase))
    {
        // 给子属性添加导航别名前缀
        selectParts.Add($"{navAlias.Quote()}.{prop.Name.Quote()} AS \"{navAlias}_{prop.Name}\"");
    }
}

2. SplitOn配置错误，Dapper无法正确拆分实体

你只给子实体的Id加了别名作为SplitOn列，但子实体的其他属性没有和SplitOn列形成连续的列块，Dapper不知道哪些列属于子实体。另外，子实体的Id属性对应的列是{navAlias}_Id，和属性名Id不匹配，导致子实体的Id无法被正确赋值。

修复方案：

• 保留子实体Id的原始列名（用于映射属性），同时添加带前缀的列作为SplitOn标识
• 确保SplitOn列是子实体列块的起始列，让Dapper能正确拆分出子实体对象

// 替换子实体Id的处理代码
var childIdProp = "Id";
var childSplitOnCol = $"{navAlias}_{childIdProp}";
// 先添加用于映射子实体Id的列，再添加用于SplitOn的列
selectParts.Add($"{navAlias.Quote()}.{childIdProp.Quote()} AS \"{childIdProp}\", {navAlias.Quote()}.{childIdProp.Quote()} AS \"{childSplitOnCol}\"");
splitOnColumns.Add(childSplitOnCol);

3. 补充：更简洁的映射方式

如果你不想手动处理前缀，也可以调整select语句的顺序：先列完父实体的所有列，再列子实体的所有列（不带前缀），这样SplitOn只需要指定子实体的Id列名，Dapper就能自动映射子实体的属性。

比如调整后的select顺序：

-- 父实体列在前，子实体列在后
SELECT "r"."Id", "r"."ReservationStatus", "r"."Language", "r"."ReferralSource", 
       "ra"."Id", "ra"."Name", "ra"."Email"
FROM "Reservation" AS "r" LEFT JOIN "ReservationAddress" AS "ra" ON "r"."Id" = "ra"."ReferralId"
WHERE "r"."Id" = @id

此时SplitOn参数设置为"Id"（子实体的Id列），Dapper会自动把SplitOn之前的列映射到父实体，之后的映射到子实体。

最终效果 #

修改后，Dapper应该能正确拆分父和子实体，objs[0]的子集合会被正确填充，objs[1]的属性也能获取到正确的值，lookup字典去重后就能得到包含完整子集合的父实体。

内容来源于stack exchange