Fetching data in level 3 of self-referencing / parent-child relationship-CodePudding

I am programming a multilevel menu / submenu in ASP.NET MVC.

I have this table:

   Id       Name            ParentId
   ----------------------------------
    1       Menu1             null
    2       Menu2             null
    3       Submenu1-menu1     1
    4       Submenu2-menu1     1
    5       1-Level3-submenu1  3
    6       2-Level3-submenu1  3
    7       3-Level3-submenu1  3

To fetch the data using Entity Framework, I wrote this code:

var category = _context.Categories
                       .Include(p => p.SubCategories)
                       .Where(p => p.ParentId == null)
                       .ToList()
                       .Select(p => new MenuItemDto
                                        {
                                            CatId = p.Id,
                                            Name = p.Name,
                                            Child = p.SubCategories.ToList().Select(child => new MenuItemDto
                                                {
                                                    CatId = child.Id,
                                                    Name = child.Name,
                                                    Child =  child.SubCategories?.ToList().Select(grandchild => new MenuItemDto
                    {
                        CatId = grandchild.Id,
                        Name = grandchild.Name,

                    }).ToList()
                }).ToList(),
            }).ToList();

public class MenuItemDto
{
    public long CatId { get; set; }
    public string Name { get; set; }
    public List<MenuItemDto> Child { get; set; }
}

but the result is that just Menu1 and Menu2 and the children of Menu1 (Submenumen1-menu1 and Submenu2-menu1), I could not fetch 1-Level3-submenu1 and 2-Level3-submenu1 and 3-Level3-submenu1

CodePudding user response：

I would recommend simplifying the database call and building the tree in code as this would be more efficient.

To do this create a recursive method for building the children of an parent.

In the example below I am manually generating the Database list and taking the assumption of the ParentId being a string. I am also manually looping through the output and only going to 3 levels for this example. You should be aware that you can have infinite levels if you self reference.

You can see a working example of the code below at https://dotnetfiddle.net/BCyO6I

public class MenuItemDb
    {
        public long Id { get; set; }
        public string Name { get; set; }
        public string ParentId { get; set; }
    }
    
    public class MenuItemDto
    {
        public long CatId { get; set; }
        public string Name { get; set; }
        public List<MenuItemDto> Children { get; set; }
    }
        
    public class Program{
        public static void Main(string[] args){
            
            //get all data from Context
            //var categories = _context.Categories.ToList();
            
            //for the purpose of this test manually generate categories
            List<MenuItemDb> categories = new List<MenuItemDb>();
            categories.Add(new MenuItemDb() {
                Id = 1, Name = "Menu1", ParentId = null
            });
            categories.Add(new MenuItemDb() {
                Id = 2, Name = "Menu2", ParentId = null
            });
            categories.Add(new MenuItemDb() {
                Id = 3, Name = "Submenu1-menu1", ParentId = "1"
            });
            categories.Add(new MenuItemDb() {
                Id = 4, Name = "Submenu1-menu2", ParentId = "1"
            });
            categories.Add(new MenuItemDb() {
                Id = 5, Name = "1-Level3-submenu1", ParentId = "3"
            });
            categories.Add(new MenuItemDb() {
                Id = 6, Name = "2-Level3-submenu1", ParentId = "3"
            });
            categories.Add(new MenuItemDb() {
                Id = 7, Name = "3-Level3-submenu1", ParentId = "3"
            });
            
            List<MenuItemDto> menu = new List<MenuItemDto>();
            //build top level
            foreach(var child in categories.Where(w => w.ParentId == null))
            {
                MenuItemDto childDto = new MenuItemDto() {
                    CatId = child.Id,
                    Name = child.Name
                };
                AddChildren(childDto, categories);
                menu.Add(childDto);
            }
            
            foreach(var item in menu){
              Console.WriteLine(item.Name);
                foreach(var childLevel1 in item.Children){
                  Console.WriteLine(" -- "   childLevel1.Name);
                    foreach(var childLevel2 in item.Children){
                      Console.WriteLine("   -- "   childLevel2.Name);

                    }
                }
            }
        }       
        
        public static void AddChildren(MenuItemDto parent, List<MenuItemDb> categories){
            parent.Children = new List<MenuItemDto>();
            foreach(var child in categories.Where(w => w.ParentId == parent.CatId.ToString()))
            {
                var childDto = new MenuItemDto() {
                    CatId = child.Id,
                    Name = child.Name
                };
                AddChildren(childDto, categories);
                parent.Children.Add(childDto);
            }
        }
        
    }

CodePudding user response：

I think you want to create tree of categories so you can do like that:

first Get all of your category and put it in to the memory for better performance of sorting.

with this you can do it:

var unsortedCategories = _context.Categories.ToList()

and then sort your category with parentId

private async Task<List<Category>> SortCategoriesForTreeAsync(
        List<Category> source,
        int parentId = 0,
        CancellationToken cancellationToken = default)
    {
        if (source is null)
            throw new ArgumentNullException(nameof(source));

        var result = new List<Category>();

        foreach (var cat in source.Where(c => c.ParentId == parentId).ToList())
        {
            result.Add(cat);
            result.AddRange(await SortCategoriesForTreeAsync(source, cat.Id, true, cancellationToken));
        }

        if (ignoreCategoriesWithoutExistingParent || result.Count == source.Count)
            return result;

        //find categories without parent in provided category source and insert them into result
        foreach (var cat in source)
            if (result.FirstOrDefault(x => x.Id == cat.Id) is null)
                result.Add(cat);

        return result;
    }

use method like this:

var sortedAllCategory = await SortCategoriesForTreeAsync(unsortedCategories)

then when you have sorted categories you can create the tree:

private async Task<List<MenuItemDto>> CreateCategories(List<Category> allSortedCategories, int categoryId, CancellationToken cancellationToken)
{

    var categories = allSortedCategories
        .Where(c => c.ParentId == categoryId);

    var listModels = new List<MenuItemDto>();

    foreach (var category in categories)
    {
        var categoryModel = new MenuItemDto
        {
            CatId = category.Id,
            Name = category.Name,
        };

        var subCategories = await PrepareCategories(allSortedCategories, category.Id, cancellationToken);

        categoryModel.Child.AddRange(subCategories);

        categoryModel.HaveSubCategories = categoryModel.SubCategories.Any();

        listModels.Add(categoryModel);
    }

    return listModels;
}

so your can use this method to create tree like this:

var result = await PrepareCategories(sortedAllCategory, 0, cancellationToken);