I'm using Dapper to fetch and map data. I've the following query and relevant class.
SELECT pinv.* , sp.SupplierId,sp.SupplierName,sp.Contact1,sp.SupplierAddress,ac.AccountId,ac.AccountName,
(Select Count(*) from ReturnInvoices rinv Where rinv.InvoiceId = pinv.PurchaseInvoiceId) as ReturnInvoicesCount
FROM PurchaseInvoices pinv
LEFT JOIN Suppliers sp ON sp.SupplierId = pinv.SupplierId
LEFT JOIN Accounts ac on ac.AccountId = pinv.AccountId
And the class.
public class PurchaseInvoice
{
public int PurchaseInvoiceId { get; set; }
public int PurchaseOrderId { get; set; }
public string PurchaseInvoiceType { get; set; }
public int SupplierId { get; set; }
public int AccountId { get; set; }
public int ReceiptNumber { get; set; }
public int ReturnInvoicesCount { get; set; } // to map ReturnInvoiceCount by SubQuery
public DateTime PromisedDate { get; set; }
public DateTime InvoiceDate { get; set; }
public decimal InvoiceQuantity { get; set; }
public decimal Discount { get; set; }
public decimal Tax { get; set; }
public decimal ShippingCharges { get; set; }
public decimal Surcharge { get; set; }
public string PaymentMode { get; set; }
public decimal SubTotal { get; set; }
public decimal TotalPayment { get; set; }
public decimal AmountPaid { get; set; }
public decimal AmountRemaining { get; set; }
public string Comments { get; set; }
public string Status { get; set; }
public DateTime UpdatedDate { get; set; }
public Supplier Supplier { get; set; }
public ProductReceived ProductReceived { get; set; }
public Account Account { get; set; }
}
I'm not sharing other classes because they are not relevant. Now, if i run above query, I get perfect results as excepted. After fetching results the dapper returns all data just fine but ReturnInvoicesCount is not mapped (or idk something else is problem). This is how I'm using dapper.
using (SqlConnection connection = new SqlConnection(_conString))
{
var query = QueryHelper.GetPurchaseInvoicesQuery(start, end, simpleSearchText, searchTokensList);
// ignore above line, just getting query with parameters etc
var data = await connection.QueryAsync<PurchaseInvoice, Supplier, Account>(query.Query,
query.queryParams, splitOn: "SupplierId,AccountId");
return data;
}
As i said, I get correct result mapped except field ReturnInvoicesCount which is 0. While on server as can be seen ReturnInvoiceCount have a value.
