Dapper multi mapping many to many relationship

Question

I am trying to map an N-N relationship with dapper using as DB MySQL. This is more or less the code.

        var query = new StringBuilder();
        query.Append("SELECT O.Id, O.Email, O.Status, P.Name FROM Owners AS O");
        query.Append(" INNER JOIN OwnerPets OP ON OP.OwnerId = O.Id");
        query.Append(" INNER JOIN Pets AS P ON P.Id = OP.PetId");
        query.Append(" WHERE O.Status = @Status;");

        using (var dbConnection = CreateConnection())
        {
            return dbConnection.Query<Owner, IEnumerable<Pet>, Owner>(query.ToString(), (owner, pets) =>
            {
                owner.Pets = pets.ToList();
                return Owner;
            }, new { Status = status }, splitOn: "OwnerId, PetId");
        }

The query works fine in a SQL client but when I run the code above I get this exception: "When using the multi-mapping APIs ensure you set the splitOn param if you have keys other than Id. Parameter name: splitOn"

Is it even possible to map a N-N relationship having an intermediate table (OwnerPets)?...If so...what am I doing wrong?

Answer 1

I'm not sure if you can get Dapper to return the data directly as you want but you could load the data such that there is a one-to-one relationship between Owners and Pets and then follow it with LINQ query to group the Pets for each Owner.

return dbConnection
    .Query<Owner, Pet, Owner>(
        query,
        (owner, pet) =>
        {
            owner.Pets = owner.Pets ?? new List<Pet>();
            owner.Pets.Add(pet);
            return owner;
        },
        new { Status = status },
        splitOn: "Name"
    )
    .GroupBy(o => o.Id)
    .Select(group =>
    {
        var combinedOwner = group.First();
        combinedOwner.Pets = group.Select(owner => owner.Pets.Single()).ToList();
        return combinedOwner;
    });