Fill class object using Dapper

Question

public class Expenses
{
    public Category Category { get; set; }
    public PaymentModes PaymentModes { get; set; }
}

public class Category
{
    public int CategoryID { get; set; }
    public string CategoryName { get; set; }
}

public class PaymentModes
{
    public int PaymentModeID { get; set; }
    public string PaymentMode { get; set; }
}

public class ViewModel
{
   public IEnumerable<Month> Month { get; set; }
   public IEnumerable<Category> Category { get; set; }
   public IEnumerable<Expenses> Expenses { get; set; }
}

So I have these classes. Now I want to fill Expense class object using Dapper. But I always get Category and PaymentModes as null. Here is the result I am getting from DB:

Here is the method to fetch Expense Data:

public ViewModel FetchSummaryData(Expenses expenses)
{
    DynamicParameters param = new DynamicParameters();
    ViewModel viewModel = new ViewModel();
    param.Add("@flag", expenses.flag);
    var result = db.QueryMultiple("sp_Summary", param, commandType: CommandType.StoredProcedure);
    if (result != null)
    {
        viewModel.Category = result.Read<Category>();
        viewModel.Expenses = result.Read<Expenses>();
    }

    return viewModel;
}

As you can see the Category and PaymentModes for that particular ExpenseID is always null. How can I fill these object's properties using Dapper? Thank you.

`QueryMultiple` is for reading multiple result sets in one query batch. You only have one result set. — Gert Arnold, Sep 09 '18 at 20:11
Yeah I know, I was just trying. I tried Query but it didn't work. — Arpit, Sep 09 '18 at 20:15

Steve · Accepted Answer · 2018-09-09T21:33:26.377

I suppose you have an Expenses table with a relationship 1:1 with PaymentModes and Category tables. In this context your sp should return all the records from Expenses table with the Expenses fields listed first, followed by the PaymentModes fields and finally the Category fields. Something like this

select e.*, p.PaymentModeId, p.PaymentMode, c.CategoryID, c.CategoryName
from tbl_Expenses e inner join PaymentModes p on e.PaymentModeID = p.PaymentModeID
               inner join Category c on e.CategoryID = c.CategoryID
where ......

If you don't have relations with a PaymentModes and Category table but everything is stored in the single Expenses table then you don't need joins but just list all your fields in the correct order to allow the Dapper to know how to use your fields to fill the required objects

select ExpenseID, Price, ExpenseDate, ....., 
       PaymentModeID, PaymentMode,
       CategoryID, CategoryName
from tbl_Expenses
where ....

In any case you run your query with

var result = db.Query<Expenses, PaymentModes, Category, Expenses>("sp_Summary", 
             (e,p,c) => 
             {
                e.PaymentModes = p;
                e.Category = c;
                return e;
             }, splitOn: "PaymentModeID,CategoryID", param: param, commandType: CommandType.StoredProcedure);

At this point the result variable is a IEnumerable<Expense> with the correct PaymentModes and Category set.

The splitOn parameter allows Dapper to know on which field it should separate your result data in the three objects required by the call to the Query method.

So all the fields until the PaymentModeID are assigned to the Expenses variable, then the fields until the CategoryID go to the PaymentModes variable and the last fields to the Category variable passed to the lambda expression.
Inside the lambda you just need to assign the p and c variable to the appropriate field of the Expenses variable and satisfy the Func signature returning the Expenses variable

Hi,@Steve I am getting this error: "When using the multi-mapping APIs ensure you set the splitOn param if you have keys other than Id Parameter name: splitOn" Please check the resultset and code: https://imgur.com/a/pmHymmG — Arpit, Sep 09 '18 at 21:19
It seems that you have the PaymentModeID (and probably the CategoryID) two times in the updated sp — Steve, Sep 09 '18 at 21:30
Hi, @Steve I have made changes to the sp but still the same error occours. Please check sp and result: https://imgur.com/UlpKWOk — Arpit, Sep 10 '18 at 04:25
I am sorry but I can't reproduce your problem in any way. I have even created the three tables here and checked my code. It works as expected. The only thing to look for is about the names listed in the splitOn: parameter but they seems to be correct. — Steve, Sep 10 '18 at 07:46
Hi, @Steve you are right. Your code is working perfectly fine. The issue is I am getting all the categories in the first resultset "SELECT * FROM tblCategory" to bind dropdown for filtration purpose based on category. What should I do in this case? — Arpit, Sep 10 '18 at 15:35
Sorry I don't understand how this has something to do with the original question. However if you want a list of categories you use _var result = db.Query(cmdText):_ — Steve, Sep 10 '18 at 17:03
I am very interested to know the reason for the downvote. I use dapper a lot and if there is something wrong here I really need to know. But I suppose that none will try to explain — Steve, Sep 28 '18 at 11:54

Fill class object using Dapper

1 Answers1