Can't use dapper to multimap a one-to-many pair of tables with composite primary key

Question

I'm looking into dapper for an application with lots of readonly data. For explanation purposes, I've got 2 tables: Car and Wheel with a one-to-many relationship between the two. The Car table has CarID as a primary key, Wheel has a composite primary key existing of CarID and WheelIndex. I've tried the following code to retrieve all cars, with links to the wheels as follows:

public class Car
{
    public int CarId { get; set; }
    public List<Wheel> Wheels { get; set; } = new List<Wheel>();
    ...
}

public class Wheel
{
    public int CarId { get; set; }
    public int WheelIndex { get; set; }
    ...
}

public static void Main(string[] args)
{
    var connectionstring = "{insert connection string}";
    var query = "SELECT C.CarID, WheelIndex FROM Car C JOIN Wheel W ON C.CarId = W.CarId";
    using (var connection = new MySqlConnection(connectionstring)){
        var cars = connection.Query<Car, Wheel, Car>(query, (car, wheel) =>
        {
            car.Wheels.Add(wheel);
            return car;
        }, splitOn: "WheelIndex");
    }
}

This however only returns each combination of cars with one wheel in the Wheels list. Anybody has any idea what I'm doing wrong? I'm sure I'm just missing something.

EDIT: For reasons I can't fully disclose, Entity Framework is not a viable option.

Answer 1

You can absolutely do it in the query, there is no need to afterburn the data.

Here is how:

public static void Main(string[] args)
{
    var connectionstring = "{insert connection string}";
    var query = "SELECT C.CarID, WheelIndex FROM Car C JOIN Wheel W ON C.CarId = W.CarId";
    var cars = new Dictionary<int, Car>();
    using (var connection = new MySqlConnection(connectionstring))
    {
        connection.Query<Car, Wheel, Car>(query, (c, w) =>
        {
            if (!cars.TryGetValue(c.Id, out Car car))
            {
                // Car wasn't found in the dictionary
                car = c;
                cars.Add(car.id, car);
            }
            cars[c.Id].Wheels.Add(w);
            return null; // You won't need this. the result is in cars.
        }, splitOn: "WheelIndex");
    }
    // Use cars ...
}

That's pretty much the standard way of transforming your 2-dimensional Dapper/ADO result set into a hierarchy.