Dapper multipmap

Question

我的数据库中有以下内容，其中包含一个交叉点/外部参照表

​

我想将它映射到以下对象

 public class Coin : CoinBase
{


    public IEnumerable<CoinAnnouncement> Announcements { get; set; }
    public IEnumerable<CoinCategory> Categories { get; set; }


}

如果可能的话，我想在一次中也包括公告(不在数据库图像中显示)

这是我衣冠楚楚的电话

 using (var conn = new SqlConnection(connectionString))
        {
            conn.Open();
            return await conn.QueryAsync<Coin, CoinCategory, Coin>(
                @"SELECT c.CoinId, c.Userid, c.IconPath, c.LastPriceBtc, c.LastUpdatedUtc, c.Name, c.Rank,
                         c.Symbol, c.LogoPath, c.Description, c.SubReddit, c.TwitterScreenName, c.Website, c.Discord,
                         c.FacebookPage, c.Telegram
                FROM Coins c
                INNER JOIN CoinCategoriesCategories coinCat ON coinCat.CoinId = c.CoinId
                INNER JOIN CoinCategories cat ON cat.CategoryId = coinCat.CategoryID", 
                (coin, coinCat) => {

                    coin.Categories = coinCat; //problem figuring out what this line would look like
                    return coin;
                });
        }

实际上，我只想忽略外部参照/连接表，并将类别直接映射到coin对象

Answer 1

基于https://www.tritac.com/blog/dappernet-by-example的解决方案

      var lookup = new Dictionary<int, Coin>();

        using (var conn = new SqlConnection(connectionString))
        {
            conn.Open();
            var result = await conn.QueryAsync<Coin, CoinCategoryCategory, CoinCategory, Coin>(
                @"SELECT c.*, coinCat.*, cat.*
                    FROM Coins c
                    INNER JOIN CoinCategoriesCategories coinCat ON coinCat.CoinId = c.CoinId
                    INNER JOIN CoinCategories cat ON cat.CategoryId = coinCat.CategoryID", 
                (c, cat, coinCat) =>
                {

                    Coin coin;
                    if (!lookup.TryGetValue(c.CoinId, out coin))
                    {
                        lookup.Add(c.CoinId, coin = c);
                    }
                    if (coin.Categories == null)
                        coin.Categories = new List<CoinCategory>();
                    coin.Categories.Add(new CoinCategory { CategoryId = coinCat.CategoryId, Description = coinCat.Description });
                    return coin;
                }, splitOn: "CategoryId, CoinId");

            return lookup.Values;
        }