Dapper with multimapping using stored procedure

Question

This is regarding Dapper in ASP.NET MVC3.

I have two tables tblBranchMaster, tblZoneMaster in my database and two class file with same details.

tblBranchMaster(ID, ZoneID, Name);
tblZoneMaster(ID, Name);

Both table having primary key foreign key relationship.

I have a stored procedure which has following query :

Select * from tblBranchMaster;

with some other logical stuff.

Now how should i get result of zone with its related Branch in list.

I have following code base:

List<tblBranchMaster> lstResult = Query<tblBranchMaster, tblZoneMaster,  tblBranchMaster>
  (tblBranchMaster.uspGetBranchListPagination, (objBranch, objZone) => 
  { objBranch.ZoneMaster = objZone; return objBranch; }, 
   param, splitOn:"Id").ToList();

This code gives me following error:

When using the multi-mapping APIs ensure you set the splitOn param if you have keys other than Id
Parameter name: splitOn

What am I missing???

PHeiberg PHeiberg · Accepted Answer · 2013-05-09T06:55:04

The query result needs to have the same format and order as the objects that you want to populate. In this case dapper expects you to have "Id, Name, Id, Name", where the first two would be the Id and Name for branch master and the 3rd and 4th columns would be the Id and Name for zone master.

Given the following classes:

class Branch 
{
    int Id { get; set }
    string Name { get; set }
}

class Zone 
{
    int Id { get; set }
    string Name { get; set }
}

And the following SQL query format in your stored procedure:

SELECT b.Id, b.Name, z.Id, z.Name
FROM Branch b LEFT JOIN Zone z ON b.ZoneId = z.Id

You should be able to use this dapper code:

var result = connection.Query<Branch, Zone, Branch> ("myStoredProcName", 
    (objBranch, objZone) => { objBranch.ZoneMaster = objZone; return objBranch; }, 
    param, commandType: CommandType.StoredProcedure).ToList();

Dapper with multimapping using stored procedure

1 Answers