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.

enter image description here

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.

Yong Shun · Accepted Answer · 2024-05-01 09:01:22Z

2

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();

edited May 1, 2024 at 9:01

answered May 1, 2024 at 7:09

Yong Shun

54.5k6 gold badges38 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Rajiv Ranjan Over a year ago

Thank you so Yong, yes this is what I was looking for... It looks I am close to implement this. I have added this code, Following Error message comes. I am exploring it...It must be simple things. I already have these namespaces, please advise. using System.Linq; using Dapper; using Microsoft.Data.SqlClient; Error CS1061 'Boat' does not contain a definition for 'DistinctBy' and no accessible extension method 'DistinctBy' accepting a first argument of type 'Boat' could be found (are you missing a using directive or an assembly reference?)

Yong Shun Over a year ago

You can replace DistinctBy with GroupBy, for example: Gallery = x.GroupBy(y => y.GalleryID).Select(y => y.First()).ToList()

Rajiv Ranjan Over a year ago

same error it gives. do we need any other reference. Error CS1061 'Boat' does not contain a definition for 'GroupBy' and no accessible extension method 'GroupBy' accepting a first argument of type 'Boat' could be found (are you missing a using directive or an assembly reference?)

Yong Shun Over a year ago

Oops, sorry my bad, I update my answer again. Please check. Thanks.

Rajiv Ranjan Over a year ago

My friend recommended to add Collection (x.Gallery) code is working, but duplicate values are still coming. Gallery = x.Gallery.DistinctBy(y => y.GalleryID).ToList(), Textblocks = x.Textblocks.DistinctBy(y => y.TextBlockId).ToList(), Engines = x.Engines.DistinctBy(y => y.EngineId).ToList()

|

Jeremy Caney · Accepted Answer · 2024-05-04 23:16:44Z

My friend has given me this code; it works perfectly.

What was happening earlier: These objects were getting added earlier, and because of multiple joins involved with four tables, it kept on adding duplicate entries. Then we were trying to remove them using DISTINCT or GROUP methods.

But with the current solution, it does not add those. As you can see before it being added, it has a check to see if there is already an entry for the same GalleryID (or whatever your table's primary key is); if not, then it adds it. It makes the code cleaner and it brings the desired result.

// Add Gallery if not already included
if (gallery != null && !currentBoat.Gallery.Any(g => g.GalleryID == gallery.GalleryID))
{
    currentBoat.Gallery.Add(gallery);
}

public async Task<List<Boat>> GetAllVesselListings()
{
var query = @"SELECT 
boats.*, 
gallery.*,
textblock.*,
engine.*
FROM View_IybaBoats boats 
LEFT JOIN View_IybaImageGallery gallery ON boats.BoatId = gallery.GalleryBoatId
LEFT JOIN View_IybaTextBlock textblock ON boats.BoatId = textblock.TextBlockBoatId
LEFT JOIN View_IybaEngine engine ON boats.BoatId = engine.EngineBoatId
ORDER BY boats.BoatId, gallery.Sort";

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

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

// Add Gallery if not already included
if (gallery != null && !currentBoat.Gallery.Any(g => g.GalleryID == gallery.GalleryID))
{
    currentBoat.Gallery.Add(gallery);
}

// Add Textblock if not already included
if (textblock != null && !currentBoat.Textblocks.Any(t => t.TextBlockId == textblock.TextBlockId))
{
    currentBoat.Textblocks.Add(textblock);
}

// Add Engine if not already included
if (engine != null && !currentBoat.Engines.Any(e => e.EngineId == engine.EngineId))
{
    currentBoat.Engines.Add(engine);
}

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

return boatDict.Values.ToList();
}

Collectives™ on Stack Overflow

Dapper - Lists of nested objects getting repeated values

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related