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

Answer 1

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

Answer 2

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