Dapper - Lists of nested objects getting repeated values

Question

I require to have a single API Endpoint, which will fetch records from these four tables in a single JSON file. I am using an ASP.NET Core 8 Web API. Inside QueryAsync, I tried different combinations, but it did not yield the right result.

Currently, when I run I can see repeated Gallery & Textblock records, while Engine is returned fine. For example, this boat has 37 images in the Gallery table & 8 entries in the Textblock table for "BoatId": "317557".

But it shows 37x8 = 296 records in both, as shown in the picture. I am stuck here, please help. What I sense is I am not able to make the record distinct, which is leading to this problem. Please guide me.

public class Boat
{
    public string BoatId { get; set; }
    public string CreatedTimestamp { get; set; }
    public string UpdatedTimestamp { get; set; }
    public string Type { get; set; }
    public string Status { get; set; }
    public string ListingDate { get; set; }
    public string Model { get; set; }
    public string VesselName { get; set; }
    public int? PriceUSD { get; set; }
    public int LOAFeet { get; set; }
    public string Manufacturer { get; set; }
    public string Category { get; set; }

    public List<Gallery> Gallery { get; set; }
    public List<Textblock> Textblocks { get; set; }
    public List<Engine> Engines { get; set; }
}

public class Gallery
{
    public int GalleryID { get; set; }
    public string GalleryBoatId { get; set; }

    public string Image { get; set; }
    public string Title { get; set; }
    public string Description { get; set; }
    public string Featured { get; set; }

    public int Sort { get; set; }
    public string DateTimeStamp { get; set; }
}

public class Textblock
{
    public string TextBlockId { get; set; }
    public string TextBlockBoatId { get; set; }

    public string Language { get; set; }
    public string Title { get; set; }
    public string Description { get; set; }
    public int Sort { get; set; }
}

public class Engine
{
    public int EngineId { get; set; }
    public string EngineBoatId { get; set; }

    public string EngineMake { get; set; }
    public string EngineModel { get; set; }
    public string EngineYear { get; set; }
    public string EngineType { get; set; }
    public string EngineLocation { get; set; }
    public int Sort { get; set; }
}

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

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

    var boats = await _db.QueryAsync<Boat, Gallery, Textblock, Engine, Boat>
    (query, (boat, gallery, textblock, engine) =>
    {
        if (!boatDict.TryGetValue(Convert.ToInt32(boat.BoatId), out var currentBoat))
        {
            currentBoat = boat;

            currentBoat.Gallery = new List<Gallery>();
            currentBoat.Textblocks = new List<Textblock>();
            currentBoat.Engines = new List<Engine>();

            currentBoat.Engines.Add(engine);

            boatDict.Add(Convert.ToInt32(currentBoat.BoatId), currentBoat);
        }

        currentBoat.Gallery.Add(gallery);
        currentBoat.Textblocks.Add(textblock);

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

    return boats.Distinct().ToList();
}

I have tried placing these inside the if (!boatDict.TryGetValue... statement, then it has only one value for each.

currentBoat.Gallery.Add(gallery);
currentBoat.Textblocks.Add(textblock);

I need to make Gallery & Textblocks to keep only distinct values, but it is not happening. Please suggest.

I have gone through many posts on this website, but the result is not coming correctly as expected.

Answer 1

After getting the boats via .QueryAsync(), you can perform group/distinct the records in the projection.

var boats = await _db.QueryAsync<Boat, Gallery, Textblock, Engine, Boat>QueryAsync(/* ... */);

boats = boats.Select(x => new Boat
    {
        Gallery = x.Gallery.DistinctBy(y => y.GalleryID).ToList(),
        Textblocks = x.Textblocks.DistinctBy(y => y.TextBlockId).ToList(),
        Engines = x.Engines.ToList()
    })
    .ToList();

return boats.Distinct().ToList();

Note that .DistinctBy() is available from .NET 6. If this method doesn't support in your current framework, you should work with .GroupBy().

boats = boats.Select(x => new Boat
    {
        Gallery = x.Gallery.GroupBy(y => y.GalleryID)
            .Select(y => y.First())
            .ToList(),
        Textblocks = x.Textblocks.GroupBy(y => y.TextBlockId)
            .Select(y => y.First())
            .ToList(),
        Engines = x.Engines.ToList()
    })
.ToList();