How do you use Dapper's Multi-Mapping feature on two fields using the same table? i.e ClientInfo
has two Address
objects.
public class ClientInfo
{
public Guid Id => Guid.NewGuid();
public string FirstName { get; set; }
public string LastName { get; set; }
public Address PostalAddress { get; set; }
public Address BillingAddress { get; set; }
public int ContactNumber { get; set; }
}
public class Address
{
public Guid Id = Guid.NewGuid();
public string FirstLine { get; set; }
public string SecondLine { get; set; }
public string Town { get; set; }
public string PostCode { get; set; }
}
Relational - Address.Id used in ClientInfo.PostalAddress / BillingAddress
tbl.Address
|Id|FirstLine|SecondLine|Town|PostCode
tbl.ClientInfo
|Id|FirstName|LastName|PostalAddress|BillingAddress|etc..
Results only in all but PostalAddress being mapped.
var sql = @"select * from ClientInfo c left join Address as a on a.Id = c.PostalAddress left join Address as ad on ad.Id = c.BillingAddress";
var clients = connection.Query<ClientInfo, Address, Address, ClientInfo>(
sql,
(client, postal, billing) =>
{
client.PostalAddress = postal;
client.BillingAddress = billing;
return client;
},
splitOn: "PostalAddress,BillingAddress")
.Distinct()
.ToList();
return clients;
The splitOn
parameter tells Dapper when/where to start mapping the next object, so you need to ensure that your SQL query returns the information in the correct order. Right now, you return 2 guids for PostalAddress and BillingAddress. Dapper doesn't know how to map them both.
select * from ... join ...
will result in the Address
data ordered AFTER the ClientInfo.PostalAddress
and ClientInfo.BillingAddress
columns.
Try: SELECT c.Id, c.FirstName, c.LastName, c.ContactNumber, a.*, ad.* FROM ClientInfo c LEFT JOIN Address AS a ON a.Id = c.PostalAddress JOIN Address AS ad ON ad.Id = c.BillingAddress
As you can see, removing the *
effectively excludes the PostalAddress
and BillingAddress
guids from the results and we can now now splitOn: "Id,Id"
.