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");

Your `Dictionary` should be defined before your call to `Query`. Otherwise you create a new one each time and don't keep track of all the previously seen rows. — juharr, Sep 17 '18 at 11:22
I don't think the key of your dictionary needs to be nullable since the Id or your parent table should never be null. — juharr, Sep 17 '18 at 11:29
I now get many children on each parent, so thats one problem solved. Thank you. However i still get many duplicates on parents. My Poco's have nullable but sql does not. — Sebastian Lindblom At Zinzino, Sep 17 '18 at 11:32
Brilliant @juharr. Tank you, that works. Im guessing putting Distinct on the end of Query would give the same result. — Sebastian Lindblom At Zinzino, Sep 17 '18 at 11:40
Yes, using `Distinct` would also work since you're just dealing with duplicate references instead of duplicated objects. — juharr, Sep 17 '18 at 12:28

score 5 · Accepted Answer · answered Sep 17 '18 at 11:25

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");

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

1 Answers1