splitOn Error while using one to many relation in dapper

1
votes

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;
        }
    }
2
asp.netdapper
Looks like [dbo].[MenuCategories] table does not have MenuID column. - Dmitry Egorov
@DmitryEgorov, yes it is a child table. i tried with splitOn: "MenuCategoryID" but I got the same error. - Tejinder Singh
Then you need to join the two tables in this query. - Dmitry Egorov
@DmitryEgorov, so there is no way to do this without join. as entity framework works in ORM ? - Tejinder Singh
I'm afraid no. Dapper cannot know where to find VMMenu's data unless you tell it in the query. - Dmitry Egorov

2 Answers

1
votes

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.

0
votes

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;
        }
    }