c# Dapper, SplitOn: multiple same parameter issue Multi-mapping one-to-many

Question

I have an issue were Dapper Query splits on the parent object and not the child object. The result gives back one parent row per child, giving many duplicate parents with only 1 child in them.

One possible issue im exploring is if the primary key of table1 and table2 is causing some confusion in the split function, since they have the same name (.Id).

The question is, how do i split the parent-child rows so it can bind many children to one parent model using same parameters in SplitOn:?

=========================================================================

Conclusion

In the comments we concluded that the dictionary needed to be outside of the function and there was no need for multiple SplitOn:, one was enough.

Finally the dictionary did provide with a distinct collection of Table1 with each having representing children.

See comments for more details.

==========================================================================

var sql = $@"
SELECT * FROM Table1 t 
INNER JOIN Table2  c ON c.Table1_Id = t.Id;";

                var result = connection.Query<Table1, Table2, Table1>(
                   sql,
                   (table1, table2) =>
                   {
                       Table1 table1Entry;
                       Dictionary<int?, Table1> table1Dictionary = new Dictionary<int?, Table1>();

                       if (!table1Dictionary.TryGetValue(table1.Id, out tableEntry))
                       {
                           table1Entry = table1;
                           table1Entry.table2s = new List<Table2>();
                           tableDictionary.Add(table1Entry.Id, table1Entry);
                       }

                       tableEntry.table2s.Add(table2);
                       return table1Entry;
                   },
                   new
                   {

                   },
                   splitOn: "Id,Id");

Answer 1

The entire point of using a Dictionary with a one to many Dapper query is so you can keep track of previous parents. To that end it has to be defined outside of the Query call. What you have will create a new dictionary each time just before it tries a lookup in it, so it will always be empty.

Also if both tables first column is "Id" then you don't need to specify it twice in the splitOn.

Dictionary<int?, Table1> table1Dictionary = new Dictionary<int?, Table1>();
var result = connection.Query<Table1, Table2, Table1>(
    sql,
    (table1, table2) =>
    {
       Table1 table1Entry;

       if (!table1Dictionary.TryGetValue(table1.Id, out tableEntry))
       {
           table1Entry = table1;
           table1Entry.table2s = new List<Table2>();
           tableDictionary.Add(table1Entry.Id, table1Entry);
       }

       tableEntry.table2s.Add(table2);
       return table1Entry;
    },
    new
    {

    },
    splitOn: "Id");