CODEWITHSUNDEEP
c#t-sqldapper
Bo Mortensen
Bo Mortensen

Reputation: 975

Understanding Dappers splitOn property

I've only just started using Dapper for a project I'm working on. I've got it up and running and made a few, simple queries, but when it comes to joining tables and using the splitOn property, I'm a bit confused.

Say I have the following query:

Query<Firm, Firm_FirmType, FirmTypeShareholder, City, Country, Firm>(@"SELECT DISTINCT Firm.*, sh.CustomerID FROM Firm
                                    JOIN Firm_FirmType AS ft ON ft.FirmID = Firm.ID
                                    JOIN FirmTypeShareholder AS sh ON sh.Firm_FirmTypeID = ft.ID
                                    JOIN City ON City.ID = Firm.ZipCode
                                    JOIN Country ON Country.ID = Firm.ISO
                                    JOIN Person_Firm_PersonResponsibility as res ON res.FirmID = Firm.ID
                                    WHERE res.PersonID = " + personId,
                            (firm, firmType, shareholder, city, country) =>
                            {
                                firm.City = city;
                                firm.Country = country;
                                firmType.FirmTypeShareholder = shareholder;
                                firm.Firm_FirmType.Add(firmType);
                                return firm;
                            }, splitOn: "ID, ID, Firm_FirmTypeID, ID, ID");

Seeing as I only want to actually select all columns from the Firm table and only the CustomerID from the FirmTypeShareholder table, how exactly should my splitOn be then? Right now, I've defined the IDs to split on for each of the tables in my Query<...> which gives this:

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

exception.

Can anyone shed som light on how this is working (and what I'm doing wrong) ? :-) Thanks in advance!

Upvotes: 7

Views: 9082

Answers (1)

Bo Mortensen
Bo Mortensen

Reputation: 975

Found out myself after hours of headache :-) Simply by naming the IDs, I made it work:

return conn.Query<Firm, Firm_FirmType, FirmTypeShareholder, City, Country, Firm>(@"SELECT DISTINCT Firm.*, ft.*, sh.CustomerID, ci.ID AS cityId, ci.ZipCode, ci.City, co.ID AS countryId, co.Country  FROM Firm
                                    JOIN Firm_FirmType AS ft ON ft.FirmID = Firm.ID
                                    JOIN FirmTypeShareholder AS sh ON sh.Firm_FirmTypeID = ft.ID
                                    JOIN City AS ci ON ci.ID = Firm.ZipCode
                                    JOIN Country AS co ON co.ID = Firm.ISO
                                    JOIN Person_Firm_PersonResponsibility as res ON res.FirmID = Firm.ID
                                    WHERE res.PersonID = " + personId,
                            (firm, firmType, shareholder, city, country) =>
                            {
                                firm.City = city;
                                firm.Country = country;
                                firmType.FirmTypeShareholder = shareholder;
                                firm.Firm_FirmType.Add(firmType);
                                return firm;
                            }, splitOn: "ID,ID,CustomerID,cityId,countryId");

Upvotes: 3

Related Questions