Dapper SplitOn Repeating Classes

Question

I fear I may be misunderstanding multi-mapping in Dapper.

Given the following:

public class Job
{
    public int JobId { get; set; }
    public Site FromSite { get; set; }
    public Site ToSite { get; set; }
}

public class Site
{

    public int SiteId { get; set; }
    public string CompanyName { get; set; }
    public Address Address { get; set; }
}
public class Address
{
    public string Address1 { get; set; }
    public string Postcode { get; set; }
}

Each Job will have 2 instances of Site, each of which will have an instance of an Address

This is flattened and I'm attempting to map this as below:

    string sql = @"
                    select 
                        JOBIdNumber JobId, 

                        fr.SUPKey SiteId, 
                        fr.SUPCompanyName CompanyName, 

                        fr.SUPAddress1 Address1, 
                        fr.SUPPostCode Postcode, 

                        t.SUPKey SiteId, 
                        t.SUPCompanyName CompanyName, 

                        t.SUPAddress1 Address1, 
                        t.SUPPostCode PostCode

                    FROM tb_JobJob inner Join tb_SUPSupplier fr on JOBFromSiteId = fr.SupKey
                    inner join tb_SUPSupplier t on t.SUpkey = jobTositeId
                    where jobidnumber = @JobId
                ";

    var jb = cn.Query<Job, Site, Address, Site, Address, Job>(sql,
        (job, fromSite, fromSiteAddress, toSite, toSiteAddress) =>
    {
        job.FromSite = fromSite;
        job.FromSite.Address = fromSiteAddress;

        job.ToSite = toSite;
        job.ToSite.Address = toSiteAddress;

        return job;
    },
    splitOn: "SiteId,Address1",
    param: new { JobId = jobId });

Which results in:

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

I'm not sure whether I am misunderstanding the process, and if so, how do I go about mapping this, it seems like it should be something others have come across.

Answer 1

You don't give dapper enough information to split the result columns into the respective objects.

    string sql = @"
                    select 
                        1 as JobId, 
                        2 as SiteId, 
                        'Company 1' as CompanyName, 
                        'Address 1' as Address1, 
                        'Postcode 1' as Postcode, 
                        3 as SiteId, 
                        'Company 2' as CompanyName, 
                        'Address 2' as Address1, 
                        'Postcode 2' as PostCode
                ";

   var jb = connection.Query<Job, Site, Address, Site, Address, Job>(sql,
                    (job, fromSite, fromSiteAddress, toSite, toSiteAddress) =>
                    {
                        job.FromSite = fromSite;
                        job.FromSite.Address = fromSiteAddress;

                        job.ToSite = toSite;
                        job.ToSite.Address = toSiteAddress;

                        return job;
                    },
                    splitOn: "JobId,SiteId,Address1,SiteId,Address1");

Does the job.

splitOn means "put everything UP and including the split field into the respective object. Because you have multiple child classes, you need to define a split field for each of them.