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

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

Answer 1

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.

Answer 2

Navigation properties always have backing foreign keys. You can declare the foreign keys explicitly and leverage them in queries. If you add the parent foreign key to the model class then you can filer on ParentKey == null to get only root items. You're not adding a new column, as this column already exists in some form, your just including it explicitly in your model so it can be used in queries. What you parent key is named and what it will be named when you add it to the model depends on your mapping. There's plenty of questions on mapping these types of keys.

Here is a similar example, notice ParentId declared in the model: Ordering a self-referencing hierarchy in LINQ

I always include all foreign keys in the model because it becomes handy for certain types of queries or update operations where setting the FK is more efficient than querying the related object to create a relationship. It also ensures your FK columns aren't strangely named by EF.

Answer 3

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();