Recursive query takes too much time to load data in C#

Question

*I have written a recursive query to get unlimited menu layer. The query works fine providing the exact results but it takes too much time to load. It takes probably 10 to 15 seconds. Please help me if I need to do anything to improve the performance. I have provided all the code to find out the problem. for mapping from entity to view model I have used automapper. *

Entity:

public class Menus
    {
        public int Id { get; set; }
        public string Icon { get; set; }
        public string Label { get; set; }
        public string To { get; set; }
        [ForeignKey("Parents")]
        public int? ParentsId { get; set; }
        public string Key { get; set; }
        public bool? Permitted { get; set; }
        public Menus Parents { get; set; }
        public ICollection Subs { get; set; }
        public ICollection MenusRoles { get; set; }
    }

Query:

public async Task> GetAllMenusAsync()
        {
            List temp = await ApplicationDbContext
                .Menus
                .Include(x => x.Subs)
                .Where(x => x.Parents == null)
                .Select(f => new Menus
                {
                    Id = f.Id,
                    Key = f.Key,
                    Label = f.Label,
                    To = f.To,
                    Icon = f.Icon,
                    ParentsId = f.ParentsId,
                    Subs = f.Subs
                }).ToListAsync();
            return Get_all_menus(temp);
        }


public List Get_all_menus(List menus)
        {
            int z = 0;
            List menuList = new List();
            if (menus.Count > 0)
            {
                menuList.AddRange(menus);
            }
            foreach (Menus item in menus)
            {
                Menus menu = ApplicationDbContext
                    .Menus
                    .Include(y => y.Subs)
                    .Where(y => y.Id == item.Id)
                    .Select(y => new Menus
                    {
                        Id = y.Id,
                        Key = y.Key,
                        Label = y.Label,
                        To = y.To,
                        Icon = y.Icon,
                        ParentsId = y.ParentsId,
                        Subs = y.Subs,
                        Permitted = true
                    }).First();

                if (menu.Subs == null)
                {
                    z++;
                    continue;
                }

                List subMenu = menu.Subs.ToList();
                menu.Subs = Get_all_menus(subMenu);
                menuList[z] = menu;
                z++;
            }
            return menuList;
        }

In Controller

 [HttpGet("get-all-menus")]
        public async Task GetAllMenus()
        {
            var menus = await _menusService.GetAllMenus();
            var menusResources = _mapper.Map, IEnumerable>(menus);
            return Ok(menusResources);
        }

View Model

public string Id { get; set; }
        public string Icon { get; set; }
        public string Label { get; set; }
        public string To { get; set; }
        public bool? Permitted { get; set; }
        public ICollection Subs { get; set; }

user_2363967 · Answer

Instead of loading the root menus, then loading the children in separate queries, just load the whole collection in one query, and then populate the navigation links afterwards.

public async Task> GetAllMenusAsync()
{
    List temp = await ApplicationDbContext.Menus.ToList();
    List topLevel = new List();

    foreach (var menu in temp)
    {
        if (menu.ParentsId == null)
        {
            topLevel.Add(menu);
            continue;
        }

        var parent = temp.Find(x => x.Id == temp.ParentsId.Value);
        if (parent.Subs == null)
            parent.Subs = new List();

        parent.Subs.Add(menu);
    }

    return topLevel;
}

user_1410664 · Answer

You should just be able to do:

context.Menus.Include(m => m.Subs).ToList();

The relationship fixup in EFCore will link all the menus together in a tree for you. In later EFs you don't even need the Include..

context.Menus.ToList();

Here is a table in SSMS:

Here is the data:

Here it is chopped up in a paint program and rearranged into a tree:

Here's the scaffolded entity:

//  This file has been auto generated by EF Core Power Tools. 
#nullable disable
using System.Collections.Generic;

namespace ConsoleApp7net5.Models
{
    public partial class Menu
    {
        public Menu()
        {
            InverseParent = new HashSet();
        }

        public int Id { get; set; }
        public int? ParentId { get; set; }
        public string Label { get; set; }

        public virtual Menu Parent { get; set; }
        public virtual ICollection InverseParent { get; set; }
    }
}

Here's what we see after asking EFC (5, in my case) to download it all with just a ToList:

Of course it might make sense to start with a root (or multiple roots but my data only has one)

Don't give classes plural names (Menus), btw, and don't give properties plural names if they aren't collections/enumerables (Parents) - it makes for very confusing code

Recursive query takes too much time to load data in C#

2 Answers