samedi 23 avril 2016

Need a alternate approch to do SQL to LINQ conversion

I'm trying to get a particular result set for my View to bind. I'm new to Linq expression, so I'm not very sure about the different ways of doing it.

Here is my MenuModel

public class MenuModel : DisposeBase
    {
        public string ParentID { get; set; }
        public string ParentName { get; set; }
        public List<MenuItemModel> MenuItems { get; set; }
    }

My MenuItemModel

public class MenuItemModel : DisposeBase
    {
        public string ChildID { get; set; }
        public string ChildName { get; set; }
        public string PageURL { get; set; }
    }

MenuModel is the output type I'm expecting as a result set. I'm getting result set of type DataTable from backend

DataTable dtable = oDatabase.ExecuteAdapter(System.Data.CommandType.StoredProcedure, "SP_GETUSERNAVMENUDATA");

Here is my SQL result set,

My DataTable will looks like this

Now I need to convert this Datatable to type MenuModel.

I tried to Query distinct MenuModel and based on that I'm building MenuItemModel object.

List<MenuModel> lstMenuModel = dtable.DataTableToList<MenuModel>()
                                            .GroupBy(p => new { p.ParentID, p.ParentName })
                                            .Select(g => g.First())
                                            .ToList<MenuModel>();

            foreach (MenuModel parentItem in lstMenuModel)
            {
                List<MenuItemModel> lstUserMenuItemData = dtable.DataTableToList<MenuItemModel>()
                                                .Select(i => new { i.ChildID, i.ChildName, i.PageURL, i.ParentID })
                                                .Where(i => i.ParentID.Equals(parentItem.ParentID))
                                                .ToList<MenuItemModel>();
            }

But still I'm getting conversion error while building MenuItemModel. Now I wanted to know, is there any best practice to do this same conversion of these nested class type? I'm sure there should be something simple to do so.

Any help could be appreciated. Thanks!

Note: DataTableToList is a method that will convert DataTable object to specific generic type

Aucun commentaire:

Enregistrer un commentaire