如何用泛型结合Dapper的SplitOn实现一对多关系映射？

问题背景 #

数据库包含Departments和People表，People通过DepartmentId与Departments建立关联，单条人员数据对应单个部门。已定义如下模型：

public class PersonModel
{
    public int Id { get; set; }
    public string FullName { get; set; }
    public DepartmentModel Department { get; set; }
}

public class DepartmentModel
{
    public int Id { get; set; }
    public string Name { get; set; }
}

非泛型场景下，可通过Dapper的SplitOn参数实现关联对象映射：

public async Task<IEnumerable<PersonModel>> GetPeople(string connectionString)
{
     using var connection = new SqlConnection(connectionString);

     var query = @"SELECT People.Id, People.FullName, 
                         Departments.Id, Departments.Name 
                  FROM People 
                  LEFT JOIN Departments ON People.DepartmentId = Departments.Id";

    return await connection.QueryAsync<PersonModel, DepartmentModel, PersonModel>(
        query, 
        (person, department) => {
            person.Department = department;
            return person;
        }, 
        splitOn: "Id" 
    );
}

但在泛型改造时，因无法预先知晓主模型中子属性的名称（如PersonModel的Department），无法直接完成赋值操作，导致泛型方法无法正常运行。

可行实现方案 #

方案1：传递属性名称，基于反射赋值 #

通过调用方传入主模型中子属性的名称，利用反射完成赋值：

public async Task<IEnumerable<TModel>> GetWithSubModel<TModel, TSubModel>(
    string connectionString, 
    string query, 
    string subPropertyName)
{
    using var connection = new SqlConnection(connectionString);

    return await connection.QueryAsync<TModel, TSubModel, TModel>(
        query, 
        (model, subModel) => {
            var property = typeof(TModel).GetProperty(subPropertyName);
            if (property != null && property.PropertyType == typeof(TSubModel))
            {
                property.SetValue(model, subModel);
            }
            return model;
        }, 
        splitOn: "Id"
    );
}

调用示例：

var people = await GetWithSubModel<PersonModel, DepartmentModel>(
    connectionString, 
    @"SELECT People.Id, People.FullName, 
             Departments.Id, Departments.Name 
      FROM People 
      LEFT JOIN Departments ON People.DepartmentId = Departments.Id",
    "Department");

方案2：传递赋值委托，直接定义映射逻辑 #

让调用方传入一个委托，显式指定主模型与子模型的赋值关系，这种方式更直观且无需反射：

public async Task<IEnumerable<TModel>> GetWithSubModel<TModel, TSubModel>(
    string connectionString, 
    string query, 
    Func<TModel, TSubModel, TModel> mapFunction)
{
    using var connection = new SqlConnection(connectionString);

    return await connection.QueryAsync<TModel, TSubModel, TModel>(
        query, 
        mapFunction, 
        splitOn: "Id"
    );
}

调用示例：

var people = await GetWithSubModel<PersonModel, DepartmentModel>(
    connectionString, 
    @"SELECT People.Id, People.FullName, 
             Departments.Id, Departments.Name 
      FROM People 
      LEFT JOIN Departments ON People.DepartmentId = Departments.Id",
    (person, department) => {
        person.Department = department;
        return person;
    });

方案3：表达式树缓存反射逻辑（高性能场景） #

如果该泛型方法会被频繁调用，可通过表达式树提前编译赋值逻辑，避免每次反射带来的性能损耗：

// 缓存已编译的赋值委托
public static class MappingSetterCache
{
    private static readonly Dictionary<(Type MainType, Type SubType, string PropName), Action<object, object>> _setterCache = new();

    public static Action<object, object> GetSetter(Type mainType, Type subType, string propertyName)
    {
        var cacheKey = (mainType, subType, propertyName);
        if (_setterCache.TryGetValue(cacheKey, out var setter))
        {
            return setter;
        }

        var property = mainType.GetProperty(propertyName);
        if (property == null || property.PropertyType != subType)
        {
            throw new ArgumentException($"Property {propertyName} not found on {mainType.Name} or type mismatch.");
        }

        // 构建表达式树
        var mainParam = Expression.Parameter(typeof(object), "mainModel");
        var subParam = Expression.Parameter(typeof(object), "subModel");
        var castMain = Expression.Convert(mainParam, mainType);
        var castSub = Expression.Convert(subParam, subType);
        var assignExpr = Expression.Assign(Expression.Property(castMain, property), castSub);
        
        var lambda = Expression.Lambda<Action<object, object>>(assignExpr, mainParam, subParam);
        setter = lambda.Compile();
        _setterCache[cacheKey] = setter;
        return setter;
    }
}

// 泛型查询方法
public async Task<IEnumerable<TModel>> GetWithSubModel<TModel, TSubModel>(
    string connectionString, 
    string query, 
    string subPropertyName)
{
    using var connection = new SqlConnection(connectionString);
    var setter = MappingSetterCache.GetSetter(typeof(TModel), typeof(TSubModel), subPropertyName);

    return await connection.QueryAsync<TModel, TSubModel, TModel>(
        query, 
        (model, subModel) => {
            setter(model, subModel);
            return model;
        }, 
        splitOn: "Id"
    );
}

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