How do I load a recursive entity without also loading the children at root level?

c# entity-framework-core linq

Question

If you look at the CoreUI live demo website, you'll see a navigation bar on the left-hand side, with multiple collapsible levels. I want to implement something like this, but dynamically. I created a simple class:

public class NavItem
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int navItemId { get; set; }
    public int sortOrder { get; set; }
    public bool isTitle { get; set; }
    public bool isDivider { get; set; }
    public string cssClass { get; set; }
    public string name { get; set; }
    public string url { get; set; }
    public string icon { get; set; }
    public string variant { get; set; }
    public string badgeText { get; set; }
    public string badgeVariant { get; set; }
    public virtual ICollection<NavItem> children { get; set; }
}

Note the ICollection<NavItem> children property.

Anyway, I populated this with a sample data set (the CoreUI example), and it correctly saves in the database, with a field called NavItemId1 storing the ID of the parent of any children. All good so far.

Now I'd like to query it, so I did the obvious:

var nI = db.navItems.ToList();

This, rather brilliantly, produces a list containing all of the nav items, with the children property correctly populated with children, where required.

However, it also included all of the child items at root level too... so instead of 15 root level items, I've got 40.

Is there a linq query I can run which will prevent the root level of the list from filling up with children (i.e. excluding any where the field NavItemId1 != null), but still correctly loading the rest of the structure?

e.g. this is what I get now:

  • root1
  • root2
    • child1-of-root2
    • child2-of-root2
      • child1-of-child2-of-root2
  • root3
    • child1-of-root3<-- I want my list to end here
  • child1-of-root2
  • child2-of-root2
    • child1-of-child2-of-root2
  • child1-of-child2-of-root2
  • child1-of-root3

I could add an isRoot boolean property, then run a post-read query to drop any items at root level which don't have isRoot set, e.g.

var nI = db.navItems.ToList();
nI = nI.Where(p => p.isRoot).ToList();

but that seems very bodgy.

Example code showing the problem. In particular look at items 6 and 16 (1st with 1 level of children, 2nd with 2 levels).

https://github.com/adev73/EFCore-LoadParentWithChildren-Example

1
2
5/30/2018 10:30:38 PM

Accepted Answer

The result that you get is correct. You request all navItems as a list, and you get a list with all navItems. The fixup of the child nodes is a nice 'bonus' that EF did for you.

You also correctly apply the filter on the instantiated list instead of the dataset directly. Were you to apply it on the dataset, your child nodes would not have been polulated! Then, to populate the child nodes you would have to use Includes, but that would only get you 1 level, which might not be enough.. of course you could add multiple includes but this would not scale well...

Your "bodgy" way of doing it seems not so bodgy after all.. I find it rather elegant.

2
5/30/2018 9:05:21 PM

Popular Answer

I might be wrong, but, if you have children, don't you need a parent?

public class NavItem
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int navItemId { get; set; }
    public int? parentNavItemId { get; set; }
    public int sortOrder { get; set; }
    public bool isTitle { get; set; }
    public bool isDivider { get; set; }
    public string cssClass { get; set; }
    public string name { get; set; }
    public string url { get; set; }
    public string icon { get; set; }
    public string variant { get; set; }
    public string badgeText { get; set; }
    public string badgeVariant { get; set; }

    public NavItem parentNavItem { get; set; }
    public virtual ICollection<NavItem> children { get; set; }
}

Then you would filter by those without a parent:

var items = db.NavItems
    .ToList() // get every item so that relations load correctly
    .Where(x => x.parentNavItemId == null) // then filter by top-level
    .ToList();


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow