c# - 使用 Dapper 在自定义 Poco 对象中返回模型

Question

我正在使用以下查询聚合数据:

var result = Connection.Query<TransactionStatsByUserGrouped>(
    @"SELECT usr.*, st.Amount, st.Count
    FROM Users usr
    RIGHT JOIN (select UserId, sum(Amount) as Amount, sum(Count) Count
        FROM (
            SELECT User2Id as UserId, sum(Amount) as Amount, count(TransactionId) Count
            FROM Transactions
            WHERE User1Id = @UserId
            GROUP BY User2Id
    ) t GROUP BY UserId) st
    ON st.UserId = usr.UserId
    ORDER BY st.Amount DESC",
    param: new { UserId = userId },
    transaction: Transaction
);

自定义 Poco 对象具有以下结构:

public class TransactionStatsByUserGrouped
{
    public User User { get; set; }
    public decimal Amount { get; set; }
    public int Count { get; set; }
}

其中 User 是一个实际的数据模型，包含以下属性:

public class User
{
    public string UserId { get; set; }
    public string Email { get; set; }
    public int Role { get; set; }
    public string Password { get; set; }
    // ...
}

我遇到的问题是，我在 TransactionStatsByUserGrouped 类中得到了 User 模型的 null 结果:

[
    {
        "user": null,
        "amount": 400.00,
        "count": 2
    },
    {
        "user": null,
        "amount": 100.00,
        "count": 1
    }
]

问题似乎在于，自定义 TransactionStatsByUserGrouped 类将模型用作属性，而不是将所有模型属性列为 TransactionStatsByUserGrouped 中的单独属性> 类。有解决办法吗？我不想手动映射每个和单个 User 模型属性。

我想在单个查询中返回用户的所有属性 + 每个属性的聚合统计信息。

.Net Core 2 + Dapper + MySQL connector (MariaDB)

Answer 1

根据文档，您可以尝试 Multi Mapping

var sql = 
  @"SELECT usr.*, st.Amount, st.Count
    FROM Users usr
    RIGHT JOIN (select UserId, sum(Amount) as Amount, sum(Count) Count
        FROM (
            SELECT User2Id as UserId, sum(Amount) as Amount, count(TransactionId) Count
            FROM Transactions
            WHERE User1Id = @UserId
            GROUP BY User2Id
    ) t GROUP BY UserId) st
    ON st.UserId = usr.UserId
    ORDER BY st.Amount DESC";

var result = Connection.Query<TransactionStatsByUserGrouped, User, TransactionStatsByUserGrouped>(
    sql,
    (group, user) => { group.User = user; return group;},
    param: new { UserId = userId },
    transaction: Transaction
);