Dapper is not mapping 1-to-many entities correctly

Your GetByIdAsync method is really convoluted, it's really hard to read. If the order of columns in the SELECT statement is proper, then you have and issue in the mapping part of the method.

When using Dapper multi-mapping you have to remember that:

1. Order of columns in SELECT statement matters.

2. Dapper splits data set on each splitting column.

3. Dapper does not cache results that it already produced.

4. If there are all NULLs in a row for a complex type, Dapper does not produce instance of this complex type.

5. If multi-mapping maps two types there should be one splitting column, when mapping three types there should be two splitting columns, and so on. One exception from that rule: when all splitting columns are named "id" you can use single "id" as a value for splitOn argument (instead of "id,id,id" for four types mapping).

To get better understanding how multi-mapping in dapper works, how powerful it is and how to efficiently populate two unrelated child collections, check this ultimate multi-mapping example:

The database (PostgreSQL) was created with the following sql:

CREATE TABLE public.house
(
  id serial NOT NULL,
  address varchar(250) NOT NULL,
  size varchar(50) NOT NULL,
  CONSTRAINT house_pkey PRIMARY KEY (id)
);

CREATE TABLE public.occupant
(
  id serial NOT NULL,
  house_id integer NOT NULL,
  name varchar(250) NOT NULL,
  CONSTRAINT occupant_pkey
    PRIMARY KEY (id),
  CONSTRAINT occupant_house_fkey
    FOREIGN KEY (house_id)
    REFERENCES public.house(id)
);

CREATE TABLE public.car
(
  id serial NOT NULL,
  occupant_id integer NOT NULL,
  brand varchar(250) NOT NULL,
  CONSTRAINT car_pkey
    PRIMARY KEY (id),
  CONSTRAINT car_occupant_fkey
    FOREIGN KEY (occupant_id)
    REFERENCES public.occupant(id)
);

CREATE TABLE public.room
(
  id serial NOT NULL,
  house_id integer NOT NULL,
  name varchar(250) NOT NULL,
  CONSTRAINT room_pkey
    PRIMARY KEY (id),
  CONSTRAINT room_house_fkey
    FOREIGN KEY (house_id)
    REFERENCES public.house(id)
);

INSERT INTO public.house(id, address, size)
VALUES (101, 'Fine Avenue 5', 'big'), (102, 'Gray Line 17', 'small');

INSERT INTO public.occupant(id, house_id, name)
VALUES (201, 101, 'Daniel Newlin'), (202, 101, 'Penelope Newlin'), (203, 102, 'Cristiano Touya');

INSERT INTO public.car(id, occupant_id, brand)
VALUES (301, 201, 'Audi'), (302, 201, 'Ford'), (303, 203, 'Mazda');

INSERT INTO public.room(id, house_id, name)
VALUES (401, 101, 'Kitchen'), (402, 101, 'Living room');

The complete console application (.NET 9, implicit usings and nullables enabled) is:

using System.Data;
using System.Diagnostics.CodeAnalysis;
using System.Text.Json;
using Dapper;
using Npgsql;

// This sample demonstrates how to use Dapper to query a PostgreSQL database
// and map the results to C# objects.
// It uses a multi-mapping technique to handle complex queries.

// Enable MatchNamesWithUnderscores to allow Dapper to match database column names with C# properties that use underscores.
DefaultTypeMap.MatchNamesWithUnderscores = true;

Console.WriteLine("Quering database...");

using var con = new NpgsqlConnection("Host=localhost;Port=5432;Username=frank;Password=frank;Database=dappertest");
await con.OpenAsync();

const string sql = """
    SELECT h.id, h.address, h.size, o.id, o.house_id, o.name, c.id, c.occupant_id, c.brand, NULL AS id, NULL AS house_id, NULL AS name
    FROM public.house h
    LEFT JOIN public.occupant o ON o.house_id = h.id
    LEFT JOIN public.car c ON c.occupant_id = o.id
    UNION ALL
    SELECT h.id, h.address, h.size, NULL, NULL, NULL, NULL, NULL, NULL, r.id, r.house_id, r.name
    FROM public.house h
    LEFT JOIN public.room r ON r.house_id = h.id
    """;

var cmd = new CommandDefinition(sql, commandType: CommandType.Text, cancellationToken: default);
var houses = new List<HouseDto>();
var houseHelper = new MultiMapHelper<HouseDto, int>(h => h.Id);
var occupantHelper = new MultiMapHelper<OccupantDto, int>(o => o.Id);
_ = await con.QueryAsync<HouseDto, OccupantDto, CarDto, RoomDto, HouseDto>(cmd,
    (house, occupant, car, room) =>
    {
        house = houseHelper.GetOrAdd(house, out bool addHouse);
        if (addHouse)
            houses.Add(house);

        if (occupant != null)
        {
            occupant = occupantHelper.GetOrAdd(occupant, out bool addOccupant);
            if (addOccupant)
                house.Occupants.Add(occupant);

            if (car != null)
                occupant.Cars.Add(car);
        }

        if (room != null)
            house.Rooms.Add(room);

        return house;
    }, splitOn: "id");

var jsonOptions = new JsonSerializerOptions { WriteIndented = true };
string json = JsonSerializer.Serialize(houses, jsonOptions);

Console.WriteLine("The result:");
Console.WriteLine(json);

internal class MultiMapHelper<TEntity, TKey>
    where TKey : notnull
{
    private readonly Func<TEntity, TKey> _keySelector;
    private readonly Dictionary<TKey, TEntity> _data;
    private TKey? _lastKey;
    private TEntity? _lastValue;

    public MultiMapHelper(Func<TEntity, TKey> keySelector)
    {
        _keySelector = keySelector;
        _data = new Dictionary<TKey, TEntity>();
    }

    public TEntity GetOrAdd(TEntity entity, out bool wasAdded)
    {
        ArgumentNullException.ThrowIfNull(entity);

        wasAdded = false;
        var key = _keySelector(entity);
        if (EqualityComparer<TKey>.Default.Equals(_lastKey, key))
            return _lastValue!;

        if (!_data.TryGetValue(key, out TEntity? cachedEntity))
        {
            _data.Add(key, (cachedEntity = entity));
            wasAdded = true;
        }

        _lastKey = key;
        _lastValue = cachedEntity;
        return cachedEntity;
    }
}

public class HouseDto
{
    private int _id;
    private string? _address, _size;
    private List<OccupantDto>? _occupants;
    private List<RoomDto>? _rooms;

    public int Id
    {
        get => _id;
        set => _id = value;
    }

    public string Address
    {
        [MemberNotNull(nameof(_address))]
        get => _address ??= String.Empty;
        set => _address = value;
    }

    public string Size
    {
        [MemberNotNull(nameof(_size))]
        get => _size ??= String.Empty;
        set => _size = value;
    }

    public List<OccupantDto> Occupants
    {
        [MemberNotNull(nameof(_occupants))]
        get => _occupants ??= new List<OccupantDto>();
        set => _occupants = value;
    }

    public List<RoomDto> Rooms
    {
        [MemberNotNull(nameof(_rooms))]
        get => _rooms ??= new List<RoomDto>();
        set => _rooms = value;
    }
}

public class OccupantDto
{
    private int _id, _houseId;
    private string? _name;
    private List<CarDto>? _cars;

    public int Id
    {
        get => _id;
        set => _id = value;
    }

    public int HouseId
    {
        get => _houseId;
        set => _houseId = value;
    }

    public string Name
    {
        [MemberNotNull(nameof(_name))]
        get => _name ??= String.Empty;
        set => _name = value;
    }

    public List<CarDto> Cars
    {
        [MemberNotNull(nameof(_cars))]
        get => _cars ??= new List<CarDto>();
        set => _cars = value;
    }
}

public class CarDto
{
    private int _id, _occupantId;
    private string? _brand;

    public int Id
    {
        get => _id;
        set => _id = value;
    }

    public int OccupantId
    {
        get => _occupantId;
        set => _occupantId = value;
    }

    public string Brand
    {
        [MemberNotNull(nameof(_brand))]
        get => _brand ??= String.Empty;
        set => _brand = value;
    }
}

public class RoomDto
{
    private int _id, _houseId;
    private string? _name;

    public int Id
    {
        get => _id;
        set => _id = value;
    }

    public int HouseId
    {
        get => _houseId;
        set => _houseId = value;
    }

    public string Name
    {
        [MemberNotNull(nameof(_name))]
        get => _name ??= String.Empty;
        set => _name = value;
    }
}

Console output is:

Quering database...
The result:
[
  {
    "Id": 101,
    "Address": "Fine Avenue 5",
    "Size": "big",
    "Occupants": [
      {
        "Id": 201,
        "HouseId": 101,
        "Name": "Daniel Newlin",
        "Cars": [
          {
            "Id": 301,
            "OccupantId": 201,
            "Brand": "Audi"
          },
          {
            "Id": 302,
            "OccupantId": 201,
            "Brand": "Ford"
          }
        ]
      },
      {
        "Id": 202,
        "HouseId": 101,
        "Name": "Penelope Newlin",
        "Cars": []
      }
    ],
    "Rooms": [
      {
        "Id": 401,
        "HouseId": 101,
        "Name": "Kitchen"
      },
      {
        "Id": 402,
        "HouseId": 101,
        "Name": "Living room"
      }
    ]
  },
  {
    "Id": 102,
    "Address": "Gray Line 17",
    "Size": "small",
    "Occupants": [
      {
        "Id": 203,
        "HouseId": 102,
        "Name": "Cristiano Touya",
        "Cars": [
          {
            "Id": 303,
            "OccupantId": 203,
            "Brand": "Mazda"
          }
        ]
      }
    ],
    "Rooms": []
  }
]

Noteworthy:

• The use of UNION ALL to return both Occupants and Rooms in one query without producing enormous numbers of rows. Database engine may process both SELECTs in parallel.

• Use of CommandDefinition that allows to pass a cancellation token.

• Use of splitOn: "id", alternatives was splitOn: "id,id,id", or omitting splitOn argument at all.

• Setting DefaultTypeMap.MatchNamesWithUnderscores so Dapper can map house_id column into HouseId property.

In Dapper, the order of columns matters when performing 1-to-many mappings using the Query method with multi-mapping. Here's what you need to know:

Id Column Position:

• The split-on column (usually the Id) must appear first in the column sequence for each entity

• Dapper splits rows when it sees a duplicate Id in this column.
  Proper Sequence Example:

  SELECT 
      u.Id, u.Name,  -- User columns (must start with Id)
      p.Id, p.Title, p.UserId  -- Post columns (must start with Id)
  FROM Users u
  JOIN Posts p ON u.Id = p.UserId