Dapper查询嵌套对象时Gallery与Textblock记录重复问题求助

问题根源 #

你的查询通过多表JOIN产生了笛卡尔积：当一个Boat关联37条Gallery和8条Textblock时，数据库会返回37×8=296条组合结果，每条结果都会触发一次Dapper的映射逻辑，导致相同的Gallery/Textblock被重复添加到列表中。

方案1：映射时通过唯一标识去重（快速修复） #

利用子实体的唯一ID（GalleryID/TextBlockId/EngineId）判断是否已添加，避免重复插入：

public async Task<List<Boat>> GetAllVesselListings()
{
    var query = @"SELECT 
        boats.BoatId As ListingBoatID, boats.*
        , gallery.GalleryBoatId As GalleryBoatID, gallery.*
        , Textblock.TextBlockBoatId As TextblockBoatID, textblock.*
        , engine.EngineBoatId As EngineBoatID, engine.*

        FROM View_IybaBoats boats 
        INNER JOIN View_IybaImageGallery gallery ON boats.BoatId = gallery.GalleryBoatId
        LEFT OUTER JOIN View_IybaTextBlock textblock ON boats.BoatId = textblock.TextBlockBoatId
        LEFT OUTER JOIN View_IybaEngine engine ON boats.BoatId = engine.EngineBoatId

        order by boats.BoatId, gallery.Sort, textblock.Sort, engine.Sort
    ";

    var boatDict = new Dictionary<int, Boat>();

    await _db.QueryAsync<Boat, Gallery, Textblock, Engine, Boat>
    (query, (boat, gallery, textblock, engine) =>
    {
        var boatId = Convert.ToInt32(boat.BoatId);
        if (!boatDict.TryGetValue(boatId, out var currentBoat))
        {
            currentBoat = boat;
            currentBoat.Gallery = new List<Gallery>();
            currentBoat.Textblocks = new List<Textblock>();
            currentBoat.Engines = new List<Engine>();
            boatDict.Add(boatId, currentBoat);
        }

        // 仅添加未存在的Gallery
        if (gallery != null && !currentBoat.Gallery.Any(g => g.GalleryID == gallery.GalleryID))
        {
            currentBoat.Gallery.Add(gallery);
        }

        // 仅添加未存在的Textblock
        if (textblock != null && !currentBoat.Textblocks.Any(t => t.TextBlockId == textblock.TextBlockId))
        {
            currentBoat.Textblocks.Add(textblock);
        }

        // 仅添加未存在的Engine
        if (engine != null && !currentBoat.Engines.Any(e => e.EngineId == engine.EngineId))
        {
            currentBoat.Engines.Add(engine);
        }

        return currentBoat;
    },
    splitOn: "ListingBoatID,GalleryID,TextBlockId,EngineId");

    return boatDict.Values.ToList();
}

关键优化点 #

• 通过boatDict确保每个Boat只被初始化一次
• 添加子实体前，用唯一ID判断是否已存在，避免笛卡尔积导致的重复插入
• 直接返回字典中的Values集合，无需额外Distinct()处理
• 增加null判断，兼容LEFT JOIN返回空值的场景

方案2：拆分查询（性能最优） #

如果子实体数量较多，多表JOIN的笛卡尔积会导致数据量暴增、性能下降。推荐拆分查询，批量获取关联数据：

public async Task<List<Boat>> GetAllVesselListings()
{
    // 1. 批量查询所有船只
    var boatQuery = "SELECT * FROM View_IybaBoats";
    var boats = (await _db.QueryAsync<Boat>(boatQuery)).ToList();
    if (!boats.Any()) return boats;

    var boatIds = boats.Select(b => b.BoatId).ToList();
    var boatIdsParam = new { BoatIds = boatIds };

    // 2. 批量查询所有Gallery并关联
    var galleryQuery = "SELECT * FROM View_IybaImageGallery WHERE GalleryBoatId IN @BoatIds ORDER BY Sort";
    var galleries = (await _db.QueryAsync<Gallery>(galleryQuery, boatIdsParam)).ToList();
    foreach (var boat in boats)
    {
        boat.Gallery = galleries.Where(g => g.GalleryBoatId == boat.BoatId).ToList();
    }

    // 3. 批量查询所有Textblock并关联
    var textblockQuery = "SELECT * FROM View_IybaTextBlock WHERE TextBlockBoatId IN @BoatIds ORDER BY Sort";
    var textblocks = (await _db.QueryAsync<Textblock>(textblockQuery, boatIdsParam)).ToList();
    foreach (var boat in boats)
    {
        boat.Textblocks = textblocks.Where(t => t.TextBlockBoatId == boat.BoatId).ToList();
    }

    // 4. 批量查询所有Engine并关联
    var engineQuery = "SELECT * FROM View_IybaEngine WHERE EngineBoatId IN @BoatIds ORDER BY Sort";
    var engines = (await _db.QueryAsync<Engine>(engineQuery, boatIdsParam)).ToList();
    foreach (var boat in boats)
    {
        boat.Engines = engines.Where(e => e.EngineBoatId == boat.BoatId).ToList();
    }

    return boats;
}

优势 #

• 完全避免笛卡尔积，数据传输量更小，性能更优
• 逻辑清晰，便于调试和维护
• 仅需4次数据库请求，远优于N+1查询模式

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