Dapper无法映射CTE查询结果至实体类的问题求助

我编写了包含CTE的SQL查询语句，定义了对应的RoleEntityV2实体类，并使用Dapper的connection.QueryAsync<RoleEntityV2>方法执行查询，但返回的实体属性值均为默认值或null；当改用connection.QueryAsync<dynamic>执行查询时，能正常获取到数据。请问我哪里操作有误？

SQL查询语句 #

with t as (SELECT
               (SELECT value from group_attribute ga2 WHERE ga2.group_id = kg.id AND ga2.name = 'description') as group_description,
               kg.id AS group_id,
               kg.name AS name,
               kg.parent_group AS parent_group,
               kg.realm_id AS realm_id,
               ga.id AS group_attr_id,
               ga.name AS group_attr_name,
               ga.value AS group_attr_value,
               ga.value AS group_attr_group_id
           FROM keycloak_group kg
                    JOIN group_attribute ga ON ga.group_id = kg.id AND ga.name = 'CompanyId'
           WHERE ga.value = '7bc5672e-9fb9-43f2-ab2e-da03dca0c32d'
           UNION
           SELECT
               (SELECT value from group_attribute ga2 WHERE ga2.group_id = kg.id AND ga2.name = 'description') as group_description,
               kg.id AS group_id,
               kg.name AS name,
               kg.parent_group AS parent_group,
               kg.realm_id AS realm_id,
               ga.id AS group_attr_id,
               ga.name AS group_attr_name,
               ga.value AS group_attr_value,
               ga.value AS group_attr_group_id
           FROM keycloak_group kg
                    JOIN group_attribute ga ON ga.group_id = kg.id
           WHERE  ga.name = 'isCommon' and ga.value = 'true')
SELECT DISTINCT  COUNT(*) over (partition by t.group_id) as members_count, (SELECT DISTINCT COUNT(*) over (partition by 1) from t) as total_count,t.*
FROM t
            JOIN user_group_membership ugm ON ugm.group_id = t.group_id

实体类代码 #

public class RoleEntityV2
{
    [Column("members_count")]
    public int MembersCount { get; set; }

    [Column("total_count")]
    public string TotalCount { get; set; }

    [Column("group_description")]
    public string Description { get; set; }
    
    [Column("group_id")]
    public Guid Id { get; set; }
    
    [Column("name")]
    public string Name { get; set; }
}

查询调用代码 #

var result = await connection.QueryAsync<RoleEntityV2>(query, new
{
    tenantKey = request.TenantKey, 
});

核心问题是Dapper不识别你当前使用的[Column]特性，你用的[Column]大概率来自EF的System.ComponentModel.DataAnnotations.Schema命名空间，而Dapper有自己的映射规则和专属特性。此外还有类型匹配、映射关系的细节问题，以下是具体解决办法：

方案1：改用Dapper专属的ColumnAttribute #

Dapper提供了自己的ColumnAttribute，位于Dapper命名空间下，替换实体类中的特性即可：

using Dapper; // 必须添加该引用

public class RoleEntityV2
{
    [Column("members_count")]
    public int MembersCount { get; set; }

    [Column("total_count")]
    public int TotalCount { get; set; } // 建议改成int，SQL返回的是数值型计数，避免类型转换问题

    [Column("group_description")]
    public string Description { get; set; }
    
    [Column("group_id")]
    public Guid Id { get; set; }
    
    [Column("name")]
    public string Name { get; set; }
}

方案2：利用Dapper默认的下划线转驼峰映射 #

Dapper默认支持将下划线命名的数据库列（如members_count）自动映射到驼峰命名的实体属性（如MembersCount），只需调整属性名匹配前缀：

public class RoleEntityV2
{
    public int MembersCount { get; set; } // 自动匹配members_count

    public int TotalCount { get; set; } // 自动匹配total_count

    public string GroupDescription { get; set; } // 自动匹配group_description
    
    public Guid GroupId { get; set; } // 自动匹配group_id，不能用Id，否则无法匹配
    
    public string Name { get; set; } // 自动匹配name
}

这种方式无需添加任何特性，适合遵循命名规范的场景。

方案3：手动配置类型映射 #

如果不想修改实体类或特性，可以通过Dapper的SqlMapper.SetTypeMap手动指定列和属性的映射关系，在查询前执行以下代码：

SqlMapper.SetTypeMap(typeof(RoleEntityV2), new CustomPropertyTypeMap(
    typeof(RoleEntityV2),
    (type, columnName) => columnName switch
    {
        "members_count" => type.GetProperty(nameof(RoleEntityV2.MembersCount)),
        "total_count" => type.GetProperty(nameof(RoleEntityV2.TotalCount)),
        "group_description" => type.GetProperty(nameof(RoleEntityV2.Description)),
        "group_id" => type.GetProperty(nameof(RoleEntityV2.Id)),
        "name" => type.GetProperty(nameof(RoleEntityV2.Name)),
        _ => null // 忽略其他未映射的列
    }
));

额外检查点 #

• 确认SQL返回的列名和映射配置完全一致，比如group_description有没有拼写错误；
• 检查group_id的数据库类型：如果数据库中是字符串类型存储的Guid，实体类用Guid类型可能无法自动转换，可临时改成string测试；
• TotalCount定义为string不合理，SQL返回的是计数数值，改成int更合适，避免不必要的类型转换问题。

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