请求Dapper多导航属性查询示例：8表关联重载方法用法

没问题！我来给你演示这个支持任意数量类型的Dapper Query重载方法，刚好能解决你关联8张表的需求。

首先，先把你之前的4表关联代码转换成这个重载的写法，帮你理解核心逻辑：

var sql = @"SELECT * FROM dbo.ComponentRelationship cr 
            JOIN dbo.ComponentRelationshipType crt ON crt.ComponentRelationshipTypeId = cr.ComponentRelationshipTypeId 
            JOIN dbo.Application fa ON fa.ApplicationId = cr.FromId 
            JOIN dbo.Application ta ON ta.ApplicationId = cr.ToId 
            ORDER BY fa.Name, ta.Name";

// 使用多类型重载方法
var result = connection.Query<ComponentRelationship>(
    sql,
    types: new[] { typeof(ComponentRelationship), typeof(ComponentRelationshipType), typeof(Application), typeof(Application) },
    map: (objects) => {
        // 将object数组中的元素转换为对应的实体类型
        var cr = (ComponentRelationship)objects[0];
        var crt = (ComponentRelationshipType)objects[1];
        var fa = (Application)objects[2];
        var ta = (Application)objects[3];
        
        // 和你之前的逻辑一样，完成关联赋值
        cr.ComponentRelationshipTypeName = crt.Name;
        cr.FromName = fa.Name;
        cr.ToName = ta.Name;
        
        return cr;
    },
    splitOn: "ComponentRelationshipId,ComponentRelationshipTypeId,ApplicationId,ApplicationId"
);

接下来，我们扩展到8张表的场景。假设你新增关联了Component、Environment、Team、Owner这4张表（对应的实体类为同名），SQL和代码可以这么写：

1. 编写包含8表关联的SQL #

SELECT * FROM dbo.ComponentRelationship cr 
JOIN dbo.ComponentRelationshipType crt ON crt.ComponentRelationshipTypeId = cr.ComponentRelationshipTypeId 
JOIN dbo.Application fa ON fa.ApplicationId = cr.FromId 
JOIN dbo.Application ta ON ta.ApplicationId = cr.ToId 
-- 新增的4张表关联
JOIN dbo.Component fc ON fc.ComponentId = fa.ComponentId 
JOIN dbo.Component tc ON tc.ComponentId = ta.ComponentId 
JOIN dbo.Team t ON t.TeamId = cr.OwnerTeamId 
JOIN dbo.Environment e ON e.EnvironmentId = cr.EnvironmentId 
ORDER BY fa.Name, ta.Name;

2. 使用多类型重载完成映射 #

var result = connection.Query<ComponentRelationship>(
    sql,
    // 传入8个实体类型的数组，顺序要和SQL查询结果中各表字段的顺序对应
    types: new[] { 
        typeof(ComponentRelationship), 
        typeof(ComponentRelationshipType), 
        typeof(Application), 
        typeof(Application),
        typeof(Component),
        typeof(Component),
        typeof(Team),
        typeof(Environment)
    },
    map: (objects) => {
        // 逐个转换object数组中的元素到对应实体
        var cr = (ComponentRelationship)objects[0];
        var crt = (ComponentRelationshipType)objects[1];
        var fa = (Application)objects[2];
        var ta = (Application)objects[3];
        var fc = (Component)objects[4];
        var tc = (Component)objects[5];
        var team = (Team)objects[6];
        var env = (Environment)objects[7];
        
        // 完成所有关联字段的赋值
        cr.ComponentRelationshipTypeName = crt.Name;
        cr.FromName = fa.Name;
        cr.FromComponentName = fc.Name;
        cr.ToName = ta.Name;
        cr.ToComponentName = tc.Name;
        cr.OwnerTeamName = team.TeamName;
        cr.EnvironmentName = env.EnvName;
        
        return cr;
    },
    // splitOn的顺序要和types数组的类型顺序对应，每个值是对应表的主键字段
    splitOn: "ComponentRelationshipId,ComponentRelationshipTypeId,ApplicationId,ApplicationId,ComponentId,ComponentId,TeamId,EnvironmentId"
);

关键注意点： #

• types数组顺序：必须和SQL查询结果中各表字段的顺序完全一致（比如先查ComponentRelationship的字段，再查ComponentRelationshipType的字段，以此类推）。
• splitOn参数：每个拆分字段对应types数组中一个类型的主键，Dapper会根据这些字段拆分结果集，将对应片段映射到types数组中的实体。
• map委托：object[]中的元素顺序和types数组顺序一致，需要手动转换为对应的实体类型，再完成业务逻辑的赋值。

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