I'm trying to use the Multimapping feature of dapper to return a list of MenuCategories and associated Menus.But I am getting below error:-
when using the multi-mapping APIs ensure you set the splitOn param if you have keys other than Id
Here are my classes:-
VMMenuCategory.cs
public class VMMenuCategory
{
public int MenuCategoryID { get; set; }
public string CategoryName { get; set; }
public System.DateTime CreatedOn { get; set; }
public DateTime? UpdatedOn { get; set; }
public List<VMMenu> Menus { get; set; }
}
VMMenus.cs
public class VMMenu
{
public int MenuID { get; set; }
public int MenuCategoryID { get; set; }
public string ProductName { get; set; }
public int? CostPrice { get; set; }
public int? SellingPrice { get; set; }
public System.DateTime CreatedOn { get; set; }
public DateTime? UpdatedOn { get; set; }
}
MenuCategoriesRepository.cs
public sealed class MenuCategoryRepository : Connection, IMenuCategoryRepository
{
List<VMMenuCategory> IMenuCategoryRepository.GetAllMenuCategories()
{
List<VMMenuCategory> _lstVMMenuCategory = new List<VMMenuCategory>();
string query = "select * from [dbo].[MenuCategories]";
using (var connection = GetConnection())
{
var data = connection.Query<VMMenuCategory, VMMenu, VMMenuCategory>(query, map:(mc,m) => { mc.Menus =new List<VMMenu>() ;return mc; },splitOn: "MenuID").ToList();
return data;
}
//return _lstVMMenuCategory;
}
}
I have changed my method in order to populate hierarchical data.
Below is the code:
public sealed class MenuCategoryRepository : Connection, IMenuCategoryRepository
{
List<VMMenuCategory> IMenuCategoryRepository.GetAllMenuCategories()
{
List<VMMenuCategory> _lstVMMenuCategory = new List<VMMenuCategory>();
string query = @"
select mc.*, m.*
from [dbo].[MenuCategories] mc
join [dbo].[Menu] m on mc.MenuCategoryID = m.MenuCategoryID";
using (var connection = GetConnection())
{
var vmMenuCategoryDictionary = new Dictionary<int, VMMenuCategory>();
var data = connection.Query<VMMenuCategory, VMMenu, VMMenuCategory>(
query,
map: (mc, m) =>
{
VMMenuCategory _VMMenuCategory;
if(!vmMenuCategoryDictionary.TryGetValue(mc.MenuCategoryID,out _VMMenuCategory))
{
_VMMenuCategory = mc;
_VMMenuCategory.Menus = new List<VMMenu>();
vmMenuCategoryDictionary.Add(_VMMenuCategory.MenuCategoryID, _VMMenuCategory);
}
_VMMenuCategory.Menus.Add(m);
return _VMMenuCategory;
},
splitOn: "MenuID").Distinct().ToList();
_lstVMMenuCategory = data;
}
return _lstVMMenuCategory;
}
}
In order to populate two objects with data from two tables you have to query both tables. In your case it's most likely an inner join:
public sealed class MenuCategoryRepository : Connection, IMenuCategoryRepository
{
List<VMMenuCategory> IMenuCategoryRepository.GetAllMenuCategories()
{
List<VMMenuCategory> _lstVMMenuCategory = new List<VMMenuCategory>();
string query = @"
select mc.*, m.*
from [dbo].[MenuCategories] mc,
join [dbo].[Menus] m on mc.MenuCategoryID = m.MenuCategoryID
";
using (var connection = GetConnection())
{
var data = connection.Query<VMMenuCategory, VMMenu, VMMenuCategory>(
query,
map:(mc,m) => {
var foundMc = _lstVMMenuCategory
.FirstOrDefault(x => x.MenuCategoryID = mc.MenuCategoryID);
if (foundMc == null) {
foundMc = mc;
foundMc.Menus = new List<VMMenu>() ;
}
foundMc.Menus.Add(m);
return mc;
},
splitOn: "MenuID").ToList();
}
return _lstVMMenuCategory;
}
}
Note: I guessed the second table name and also assumed that MenuID
comes first in the Menus
table definition. However, it's better to list all required columns explicitly in the select clause.
I also slightly amended the mapper function to avoid duplicates in the categories collection.