CODEWITHSUNDEEP
c#sql-serverdapper
Hammas
Hammas

Reputation: 1214

Dapper map subquery value to property

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.

enter image description here

Upvotes: 1

Views: 1425

Answers (1)

Alex
Alex

Reputation: 8116

You have splits on SupplierId (Maps to Supplier) and AccountId (Maps to Account). The problem is that ReturnInvoicesCount is at the wrong column position to be correctly mapped. Currently, you tell dapper that ReturnInvoicesCount belongs to Account.

Since ReturnInvoicesCount belongs to PurchaseInvoice you have to move it before SupplierId.

Upvotes: 1

Related Questions