How to map many to many relation to a list with dapper

Question

I have a class AnalysisRule

public class AnalysisRule
{
    public long Id { get; set; }
    public Analysis Analysis { get; set; }
    public AnalysisCategory AnalysisCategory { get; set; }
    public Gender Gender { get; set; }
    public bool FatherHerdBookRequired { get; set; }
    public bool MotherHerdBookRequired { get; set; }
    public List<Breed> AllowedBreeds { get; set; }
}

That has a list of Breeds

public class Breed
{
    public long BreedId { get; set; }
    public long AnimalTypeId { get; set; }
    public long BreedCode { get; set; }
    public string BreedName { get; set; }
    public string BreedAcronym { get; set; }
}

This is a many to many relationship that I bind together with a DB table AnalysisRulesBreeds

Breeds

And AnalysisRules

With Dapper I have tried

    var sql = @"select *
                from ""AnalysisRules"" 
                join ""AnalysisCategory"" on ""AnalysisRules"".""AnalysisCategoryId"" = ""AnalysisCategory"".""Id""  
                join ""Analysis"" on ""AnalysisRules"".""AnalysisId"" = ""Analysis"".""Id""
                left join ""AnalysisRulesBreeds"" on ""AnalysisRulesBreeds"".""AnalysisRuleId"" = ""AnalysisRules"".""Id""
                left join ""Breed"" on ""AnalysisRulesBreeds"".""BreedId"" = ""Breed"".""BreedId""
                where ""AnalysisId"" = :AnalysisId";
    rules = sqlConnection.QueryAsync<AnalysisRule, AnalysisCategory, Analysis, Breed, AnalysisRule>(
        sql,
        (ar, c, a, b) =>
        {
            ar.AnalysisCategory = c;
            ar.Analysis = a;
            ar.Breeds.Add(b);
            return ar;
        },
        new
        {
            AnalysisId = analysisId
        },
        splitOn:"BreedId");

Which gives me

´When using the multi-mapping APIs ensure you set the splitOn param if you have keys other than Id Parameter name: splitOn

If I run the same query in SQL Developer I get 2 rows out with same Id but with different data in Breed, so the query should be good enough.

So how do I get these 2 rows into one AnalysisRule entity where Breeds consist of 2 Breed entities?

EDIT I now have

                    sqlConnection.Open();

                var sql = @"select  ar.*,
                                    ac.*,
                                    b.*                 

                            from ""AnalysisRules"" ar
                            join ""AnalysisCategory"" ac on ar.""AnalysisCategoryId"" = ac.""Id""  
                            join ""Analysis"" a on ar.""AnalysisId"" = a.""Id""

                            left join ""AnalysisRulesBreeds""  on ""AnalysisRulesBreeds"".""AnalysisRuleId"" = ar.""Id""
                            left join ""Breed"" b  on ""AnalysisRulesBreeds"".""BreedId"" = b.""Id""

                            where ""AnalysisId"" = :AnalysisId";

                var rules = sqlConnection.QueryAsync<AnalysisRule, AnalysisCategory, Analysis, Breed, AnalysisRule>(
                    sql,
                    (ar, c, a, b) =>
                    {
                        ar.AnalysisCategory = c;
                        ar.Analysis = a;
                        ar.Breeds.Add(b);
                        return ar;
                    },
                    new
                    {
                        AnalysisId = analysisId
                    });

                return await rules;

Removed the splitOn, changed AnalysisRulesBreedsId to Id but I still get

When using the multi-mapping APIs ensure you set the splitOn param if you have keys other than Id Parameter name: splitOn

If I do the same query in SQLDev I get

Answer 1

By selecting * you get the columns of every joined table. Also you set splitOnto BreedId. Now Dapper expects that to separate the row columns of one joined table from the next, it should look for a column named BreedId.

This does not work because all tables except AnalysisRulesBreeds use Id as id column name.

Try removing the splitOn parameter, then it will default to Id. Then adjust your select-clause to only select from the tables you actually need in the result, eg.

select AnalysisRule.*, AnalysisCategory.*, Analysis.*, Breed.*

(assuming that your Analysis table and AnalysisCategory table follow the convention of having an Id column named ´Id´).