我知道這類似於在Dapper中正確使用Multimapping ,但我認為它略有不同。
我有以下POCO結構:
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; }
}
我試圖用Dapper來映射這個,我沒有使用Id作為我的分裂點。如果我把鑰匙加倍,我可以讓它工作,但我不知道為什麼我必須這樣做。
為什麼這樣做:
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");
但是這不起作用(省略了A和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");
customerinvoicekey
究竟來自哪裡?
您的模型聲稱表的關鍵是public int customerkey { get; set; }
。
如果拆分不在模型中的列,則不確定多映射函數的行為。