I know that this is similar to Proper use of Multimapping in Dapper , but I think it is a little different.
I have the following POCO structure:
public class Customer { public int customerkey { get; set; } public string FirstName { get; set; } public string LastName { get; set; } public string EmailAddress { get; set; } public List<Invoice> Invoices { get; set; } public int statekey { get; set; } public State State { get; set; } public Customer() { this.Invoices = new List<Invoice>(); } } public class Invoice { public int customerinvoicekey { get; set; } public int customerkey { get; set; } public int Number { get; set; } public string Description { get; set; } public int Total { get; set; } public int statuskey { get; set; } public State State { get; set; } } public class State { public int statekey { get; set; } public string Description { get; set; } }
I am trying to match this with Dapper, and I am not using Id for my split points. I can make it work if I double the keys, but I'm not sure why I should do this.
Why does it work:
const string commandText = @"SELECT A.customerkey, A.FirstName, A.LastName, A.EmailAddress, A.statuskey, C.statuskey, C.Description, B.customerinvoicekey, B.customerkey, B.Number, B.Description, B.Total, B.statuskey, D.statuskey, D.Description FROM Web.TestCustomers2 A INNER JOIN Web.TestCustomerInvoices2 B ON A.customerkey = B.customerkey INNER JOIN Web.TestStatus2 C ON A.statuskey = C.statuskey INNER JOIN Web.TestStatus2 D ON B.statuskey = D.statuskey ORDER BY A.customerkey"; var customers = new List<Customer>(); Customer currentCustomer = null; db.Connection.Query<Customer, State, Invoice, State, Customer>(commandText, (customer, customerstate, invoice, invoicestate) => { if (currentCustomer == null || currentCustomer.customerkey != customer.customerkey) { customers.Add(customer); currentCustomer = customer; } invoice.State = invoicestate; currentCustomer.Invoices.Add(invoice); currentCustomer.State = customerstate; return currentCustomer; }, splitOn: "statuskey,customerinvoicekey,statuskey");
But this does not work (excluding state selection in A and B):
const string commandText = @"SELECT A.customerkey, A.FirstName, A.LastName, A.EmailAddress, C.statuskey, C.Description, B.customerinvoicekey, B.customerkey, B.Number, B.Description, B.Total, D.statuskey, D.Description FROM Web.TestCustomers2 A INNER JOIN Web.TestCustomerInvoices2 B ON A.customerkey = B.customerkey INNER JOIN Web.TestStatus2 C ON A.statuskey = C.statuskey INNER JOIN Web.TestStatus2 D ON B.statuskey = D.statuskey ORDER BY A.customerkey"; var customers = new List<Customer>(); Customer currentCustomer = null; db.Connection.Query<Customer, State, Invoice, State, Customer>(commandText, (customer, customerstate, invoice, invoicestate) => { if (currentCustomer == null || currentCustomer.customerkey != customer.customerkey) { customers.Add(customer); currentCustomer = customer; } invoice.State = invoicestate; currentCustomer.Invoices.Add(invoice); currentCustomer.State = customerstate; return currentCustomer; }, splitOn: "statuskey,customerinvoicekey,statuskey");