Dapper - using multimapping with split points other than Id

Question

I know this is similar to Correct use of Multimapping in Dapper, but I think it's slightly 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 map this using Dapper and I'm not using Id for my split points. I can get it to work if I double up the keys, but I'm not sure why I have to do that.

Why does this 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 (leaving out the select of statuskey 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");

Just to be sure, I tried the latest dapper from google code with the same result. An exception is thrown - "When using the multi-mapping APIs ensure you set the splitOn param if you have keys other than Id Parameter name: splitOn" — dankorz, Oct 21 '11 at 16:21
I had a similar exception when using the spliton with a custom unique id for one specific table. I have now changed my method to create a custom poco class and let the query get only what I want rather than bring everything. — vikramjb, Aug 30 '16 at 01:34

score 1 · Answer 1 · answered Oct 23 '11 at 23:34

1

Where exactly customerinvoicekey coming from?

Your model claims the key for the table is public int customerkey { get; set; }.

If you split on a column that is not in your models the behaviour of the multi-mapping functions is undefined.

answered Oct 23 '11 at 23:34

Sam Saffron

128,308
78
326
506

Sorry, for that test, I missed customerinvoicekey in my Invoice class and updated my question above to reflect the change. The same exception is thrown. – dankorz Oct 24 '11 at 12:05

Dapper - using multimapping with split points other than Id

1 Answers1