One to many mapping on query dapper

Question

I'm trying to query one to many on dapper but for some reason I only get 1 value back from my joined table even though I should be getting multiple as from my query Can someone tell me what I did wrong?

I tried doing what was mentioned in the following post answer Mapping one to many with Dapper

My code:

 public class MonsterDatabase { public int Id { get; set; } public int MonsterId { get; set; } public string Name { get; set; } public List<MonsterLocationDatabase> Location { get; set; } } public class MonsterLocationDatabase { public int Id { get; set; } public int FkMonsterId { get; set; } public string Map { get; set; } public int Frequency { get; set; } public string MapImage { get; set; } public DateTime? DeathTime { get; set; } public DateTime? RespawnTime { get; set; } } public static MonsterDatabase GetMonster(Monster monster) { using (var connection = new SqlConnection(config["appsettings:RagnaDatabase"])) { var Monster = connection.Query<MonsterDatabase, MonsterLocationDatabase, MonsterDatabase>( "select top 1 Monster.*, SplitMonster = '', MonsterLocation.*" + " from Monster" + " join MonsterLocation" + " on Monster.Id = MonsterLocation.FkMonsterId", (Monster, Location) => { Monster.Location = new List<MonsterLocationDatabase>(); Monster.Location.Add(Location); return Monster; }, splitOn: "SplitMonster" ).FirstOrDefault(); return Monster; } }

@BarışCanYılmaz yes but I just want 1 result with the List<MonsterLocationDatabase> populated with multiple objects inside. If I do .toList() I get multiple duplicate MonsterDatabase objects with every time a single different MonsterLocationDatabase in the List<MonsterLocationDatabase> if that makes sense — Pedro Lopes
– Pedro Lopes, Commented Jan 16, 2022 at 12:24
splitOn: "SplitMonster" makes no sense because that column does not change. It needs to be a column which changes from one child object to the next. Also you have select top 1 which will only return one row anyway — Charlieface
– Charlieface, Commented Jan 16, 2022 at 14:45
Some of your code doesn't make sense here: you are declaring var Monster = ... but that seems to be the type of the monster variable being passed in to the GetMonster method. — Pierre Plourde
– Pierre Plourde, Commented Jan 16, 2022 at 22:53

Pedro Lopes · Accepted Answer · 2022-01-17 10:30:17Z

I was able to fix the issue by following the one to many dapper documentation on https://dapper-tutorial.net/result-multi-mapping

 public static MonsterDatabase GetMonster() { using (var connection = new SqlConnection(config["appsettings:RagnaDatabase"])) { var monsterDictionary = new Dictionary<int, MonsterDatabase>(); var Monster = connection.Query<MonsterDatabase, MonsterLocationDatabase, MonsterDatabase>( "select top 10 Monster.*, SplitMonster = '', MonsterLocation.*" + " from Monster" + " left join MonsterLocation" + " on Monster.Id = MonsterLocation.FkMonsterId", (Monster, Location) => { MonsterDatabase monsterEntry; if(!monsterDictionary.TryGetValue(Monster.Id, out monsterEntry)) { monsterEntry = Monster; monsterEntry.Location = new List<MonsterLocationDatabase>(); monsterDictionary.Add(monsterEntry.Id, monsterEntry); } monsterEntry.Location.Add(Location); return monsterEntry; }, splitOn: "SplitMonster" ).Distinct().FirstOrDefault(); return Monster; } }

Felipe Anselmo · Accepted Answer · 2023-07-06 23:27:48Z

Query function map will execute one time on each line result of yours locations, and you are overrinding the result every time just returning your parameter return Monster;.

The solution for you is declare a variable MonsterLocationDatabase monsterResult; above the mapping and set value just when is not null monsterResult ??= Monster; as the code below.

public static MonsterDatabase GetMonster(Monster monster) { MonsterLocationDatabase monsterResult; using (var connection = new SqlConnection(config["appsettings:RagnaDatabase"])) { connection.Query<MonsterDatabase, MonsterLocationDatabase, MonsterDatabase>( "select top 1 Monster.*, SplitMonster = '', MonsterLocation.*" + " from Monster" + " join MonsterLocation" + " on Monster.Id = MonsterLocation.FkMonsterId", map: (Monster, Location) => { monsterResult ??= Monster; if(monsterResult.Location == null) monsterResult.Location = new List<MonsterLocationDatabase>(); if (!monsterResult.Location.Any(i => i.Id == Location.Id)) monsterResult.Location.Add(Location); return monsterResult; }, splitOn: "SplitMonster"); return monsterResult; } }

Collectives™ on Stack Overflow

One to many mapping on query dapper

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related