splitOn Error while using one to many relation in dapper

Question

splitOn Error while using one to many relation in dapper

asp.net dapper

Question

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

Tejinder Singh

Popular Answer

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.

Dmitry Egorov

View more on Stack Overflow

Tejinder Singh · Accepted Answer · 5/30/2018 6:29:29 PM

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

splitOn Error while using one to many relation in dapper

Question

Fastest Dapper Plus Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator