Automapper map a table to nested class with grouping

Question

I have 2 tables: Staff & Address.
The relationship is 1 to many (assuming that 1 staff can have multiple addresses).

1. Staff
   StaffId        |       StaffName
   ++++++++++++++++++++++++++
   1                 |       Sarah
   2                 |       Steven
   3                 |       David
   
   
2. Address
   AddressId        |        StaffId        |        Street
   +++++++++++++++++++++++++++++++++++++++++++
   1                      |        1                 |        292 Oak Drive
   2                      |        1                 |        560 Pine Garden Lane
   3                      |        2                 |        1195 Randall Drive
   4                      |        2                 |        728 Ferry Street
   5                      |        2                 |        4043 Edsel Road
   6                      |        3                 |        4038 Briarwood Drive

To get staff info and their addresses, I use a LEFT JOIN query on the Address table (Dapper ORM).
Then I get data like this,

AddressId        |        StaffId        |        StaffName        |        Street
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1                      |        1                 |        Sarah               |        292 Oak Drive
2                      |        1                 |        Sarah               |        560 Pine Garden Lane
3                      |        2                 |        Steven             |        1195 Randall Drive
4                      |        2                 |        Steven             |        728 Ferry Street
5                      |        2                 |        Steven             |        4043 Edsel Road
6                      |        3                 |        David               |        4038 Briarwood Drive\

How to map this data using AutoMapper with nested grouping by staffId to DTO below?

public class StaffDto
{
    public long StaffId { get; set; }
    public string StaffName { get; set; }
    public List<Address> Address { get; set; }
}
public class Address
{
    public long AddressId { get; set; }
    public string Address{ get; set; }
}

The example data should be like this:

{
  "staffId": 1,
  "staffName": "Sarah",
  "address": 
      [
          {
            "addressId": 1,
            "street": "292  Oak Drive"
          },
          {
            "addressId": 2,
            "street": "560  Pine Garden Lane"
          }
      ]
}

Answer 1

It's a little confusing that you are joining from Address to Staff instead of the other way since you don't want a list of addresses, but a list of staff and their addresses. You don't need Automapper at all, Dapper does that nicely for you. This should do the trick:

var sql = "SELECT StaffId, StaffName. AddressId, Street FROM Staff INNER JOIN Address ON Staff.StaffId = Address.StaffId";
var staffDictionary = new Dictionary<int, StaffDto>();
var result = connection.Query<StaffDto, Adress, StaffDto>(sql,
    (s, a) => { 
        StaffDto staff;
        if (!staffDictionary.TryGetValue(s.StaffId, out staff))
        {
            staff = s;
            staffDictionary.Add(staff);
        }
        staff.Address.Add(a);
        return staff; 
    }, splitOn: "AddressId");
// Here are your staff
return staffDictionary;

The idea is that if you encounter a "new" staff, you add it to the dictionary, otherwise you just go with the one you already had. In all cases Address is added to the staff. You might be able to somehow make this work with your LEFT JOIN, but I don't see why you would.