Dapper doesn't map CTE rows-CodePudding

I have query with CTE and want to map output to class

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

And I have class entity for row

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; }
}

Querying like that

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

But when query executes, values are default or null.

But when I change query execution to await connection.QueryAsync<dynamic> - they are persisted in output.

Am I missing something?

CodePudding user response：

If we look at the output from the dynamic version, we see that the columns are things like group_id, group_description, etc. The only one that looks similar is name, and name/Name worked in the original query. Dapper cares about column names, and doesn't use [Column(...)]. If you want to map these columns, you'll either need to change the POCO so that the columns align, or change the query to include column aliases.