How to query an object with one-to-many relationship using Dapper?

Question

I have (simplified, obviously) tables such as:

public class Father
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
    public List<Son> Sons { get; set; } = new List<Son>();
}

public class Son
{
    [Key]
    public int Id { get; set; }
    public int FatherId { get; set; }
    public string Name { get; set; }
}

In the db, I have 1 Father with ID = 1, and 3 Son with FatherId = 1.

I'd have my query such that:

conn.Query<Father, Son, Father>(
    @"SELECT f.*, s.* FROM Father f INNER JOIN Son s ON f.Id = s.FatherId WHERE f.Id = 1", 
    (f, s) => 
    {
        f.Sons.Add(s);
        return f;
    },
);

The query above gives me an IEnumerable of 3 Father with each Father containing a Son, rather than what I want: a single Father object with the Sons property containing 3 Son object.

I could do this by doing this, though I am not sure if this is the best way:

// Get father
var father = await connection.GetAsync<Father>(id);
// Get related sons
var sql = $"SELECT * FROM Son a WHERE a.FatherId = {father.Id}";
var sons = (await connection.QueryAsync<Address>(sql)).AsList();
// Stitch em all together
father.Sons.AddRange(sons);

I'm wondering if this is the best way to do this. How do we get a single object containing all the related objects using Dapper?

Answer 1

There are some ways to do this, see How do I write one to many query in Dapper.Net?

I would prefer doing this as follows:

var fatherDictionary = new Dictionary<int, Father>();

var list = connection.Query<Father, Son, Father>(
    @"SELECT f.*, s.* FROM Father f INNER JOIN Son s ON f.Id = s.FatherId WHERE f.Id = 1",
    (f, s) =>
    {
        Father fatherEntry;

        if (!fatherDictionary.TryGetValue(f.Id , out fatherEntry))
        {
            fatherEntry = f;
            fatherEntry.Sons = new List<Son>();
            fatherDictionary.Add(fatherEntry.Id, fatherEntry);
        }

        fatherEntry.Sons.Add(s);
        return fatherEntry;
    })
.Distinct()
.ToList();

See example: https://dapper-tutorial.net/result-multi-mapping#example-query-multi-mapping-one-to-many