时间戳

首页 > 解决方案 > 具有多个表的 Dapper 一对多

.net - 具有多个表的 Dapper 一对多

问题描述

我让 Dapper 像这样检索我的数据：

using (var dbConnection = new SqlConnection(_connectionString))
{
    const string sql =
                "SELECT Offers.*, " +
                "       OfferDetails.*," +
                "       SomeLookup.Id AS SomeLookupId, SomeLookup.* " +
                "FROM   Offers " +
                "INNER JOIN OfferBets ON Offers.Id = OfferBets.OfferId " +
                "INNER JOIN SomeLookup ON SomeLookup.Id = Offers.SomeLookupId";

   dbConnection.Open();

   var betDictionary = new Dictionary<int, Offer>();

   return await dbConnection.QueryAsync<Offer, OfferBet, SomeLookup, Offer>(
                sql,
                (offer, bet, someLookup) =>
                {
                    if (!betDictionary.TryGetValue(offer.Id, out var offerEntry))
                    {
                        offerEntry = offer;
                        offerEntry.SomeLookup = someLookup;
                        offerEntry.Bets = new List<OfferBet>();
                        betDictionary.Add(offer.Id, offerEntry);
                    }

                    offerEntry.Bets.Add(bet);

                    return offerEntry;
                },
                splitOn: "OfferId, SomeLookupId"
            );
    }
}

它应该返回一个 Offer 列表，每个 Offer 都包含一个 OfferDetails 列表和一个 SomeLookup。

我得到的是每个 OrderDetails 对象的订单列表。它正在返回连接的数据集并为每条记录填充一个订单（并且每个记录在集合中都有 1 个 OrderDetails 项）。

我在重复检测中做错了什么？

标签： .netdapper

解决方案

我假设OfferDetails / OfferBets在 OfferId 之外还包含一个名为Id的列，并且该列放在 OfferId 之前，这会混淆。因此，如果您将splitOn参数更改为splitOn: "Id, SomeLookupId"它可能会起作用。

[Test]
public void tstAbc()
{
    using (var dbConnection = new SqlConnection(_connectionString))
    {
        const string sql = @"WITH Offers AS (

                                 SELECT * FROM (
                                     VALUES (1, 1), (2, 1), (3, 2)
                                     ) AS a (Id, SomeLookupId)
                             ),

                             OfferBets AS (

                                 SELECT * FROM (
                                     VALUES
                                         (1, 1), (2, 1), (3, 2), (4, 3)
                                     ) AS a (Id, OfferId)

                             ),

                             SomeLookup AS (

                                 SELECT * FROM (
                                     VALUES
                                         (1), (2), (3)
                                     ) AS a (Id)

                             )

                             SELECT Offers.*,
                                    OfferBets.*,
                                    SomeLookup.Id AS SomeLookupId, SomeLookup.*
                             FROM   Offers
                             INNER JOIN OfferBets ON Offers.Id = OfferBets.OfferId
                             INNER JOIN SomeLookup ON SomeLookup.Id = Offers.SomeLookupId";

        dbConnection.Open();

        var betDictionary = new Dictionary<int, Offer>();

        var res = dbConnection.Query<Offer, OfferBet, SomeLookup, Offer>(
            sql,
            (offer, bet, someLookup) =>
            {
                if (!betDictionary.TryGetValue(offer.Id, out var offerEntry))
                {
                    offerEntry = offer;
                    offerEntry.Bets = new List<OfferBet>();
                    betDictionary.Add(offer.Id, offerEntry);
                }

                offerEntry.Bets.Add(bet);
                offerEntry.SomeLookup = someLookup;

                return offerEntry;
            },
            splitOn: "Id, SomeLookupId"
        );
    }
}

推荐阅读