I have an requirement to have a single API Endpoint, which will fetch records from these fours tables in single json file. I am using .NET Core 8 Web Api. Inside QueryAsync, I tried different combinations, but it is not yielding the right result.

Currently when I run I can repeated Gallery & Textblock records, Engine comes fine. For example this boat has 37 images in Gallery table & 8 entries in Textblock table for “BoatId”: “317557”.

But it shows 37×8 = 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 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 post in this website, and code doesn’t look any issue but result is not coming.