EF Core: Eager loading (.Include) sub-categories (self-reference)

Question

We have something like this

var categories = _context.Categories.Include("Categories1.Categories1.Categories1");

That works and handles sub-categories up to 4-level deep (which is enough for now but who knows the future)

Is there a better way to do it?

More info

We use database-first. Category table has these columns:

Id
ParentCategoryId <-- this has foreign key to Category.Id

But in case you don't need `IQueryable` result, https://stackoverflow.com/questions/46160780/map-category-parent-id-self-referencing-table-structure-to-ef-core-entity can give you an idea how you can do that in memory with single db query and even w/o `Include` (but with the cost of loading the whole table in memory). — Ivan Stoev, Jul 22 '18 at 17:16

John White · Answer 1 · 2018-07-19T19:26:14.453

In this particular case I think a recursive property might be a good bet. Trying to do this from memory (been years), performance won't be great. NO lazy loading and NO explicit loading.

public class Category {
    public int Id {get; set;}
    // other stuff
    public List<Category> MyChildren {
        get { return _context.Categories.Where(x => x.ParentCategoryId == Id).ToList<Category>(); }
    } 
}

This should give you a hierarchical graph starting with a given node.

var justOne = _context.Categories.FirstOrDefault(x => x.Id = <myval>);

Downside is you will have to parse/use the result recursively and potentially it grows exponentially.

Clarification: The use of _context in the recursion is not allowed by EF and was used for illustration purposes. In the repository/UoW or business layer you could use the same technique to "assemble" the finished entity by having a property of a method that calls the method recursively.

Just for fun, here's the same recursion technique (but not as a property, don't have time right now).

public class Category       // EF entity
{
    public int Id { get; set; }
    public int ParentId { get; set; }
    public virtual List<Category> MyChildren { get; set; }
}
public static class MVVMCategory
{
    public static Category GetCategory(int id)
    {
        Category result = _context.Categories.FirstOrDefault(x => x.Id == id);

        result.MyChildren = GetChildren(id);

        return result;
    }

    public static List<Category> GetChildren(int id)
    {
        List<Category> result = _context.Categories.Where(x => x.ParentId == id).ToList<Category>();
        foreach (var item in result)
        {
            item.MyChildren = GetChildren(item.Id);
        }
        return result;
    }
}

One thing to notice. I added virtual to the list for lazy loading because I am loading children "by hand" so to speak.

Entity cannot refer _context at all, `public List MyChildren` should be somewhere else, like business logic layer or repository layer — Dongdong, Jul 19 '18 at 15:32
You are quite right. I adjusted the answer to clarify that it is a technique that should be used as a property of a method that invoked the method recursively. — John White, Jul 19 '18 at 19:07

Dongdong · Answer 2 · 2020-01-20T17:38:04.073

Firstly, add data annotations and make properties readable

public partial class Category
{

    public Category()
    {
        this.Children = new HashSet<Category>();
    }

    [Key]
    public int Id { get; set; }

    public string WhatEverProperties { get; set; }

    public int ParentCategoryId { get; set; }


    [ForeignKey("ParentCategoryId")]
    [InverseProperty("Category")]
    public Category Parent { get; set; } // name "Category1" as "Parent"

    [InverseProperty("Category")]
    public ICollection<Category> Children { get; set; } // Name it as Children
}

then, let's say we have got a category,

var category = context.Categories
    .Include(x => x.Parent)
    .Include(x => x.Children)
    .FirstOrDefault(filter);

then we get its parents with:

var rootCategory = category.Parent?.Parent?.Parent; //up-to 4 levels by your request

by following extension, we can easily get its level:

///this extension works only if you used `.Include(x => x.Parent)` from query
public static class CategoryExtensions
{
    public static int Level(this Category category)
    {
        if (category.Parent == null)
        {
            return 0;
        }

        return category.Parent.Level() + 1;
    }
}

Sorry I should have mentioned we use database-first. But thanks anyway, hope this can help others :) — Aximili, Jul 19 '18 at 00:17
I use database first too, usually I don't use "include" at all, but return the view model in query to get exactly what you need. — Dongdong, Jul 19 '18 at 15:30
It doesn't seem to be eager at all.... requires to many request to DB — Hassan Faghihi, Jan 18 '20 at 09:14

EF Core: Eager loading (.Include) sub-categories (self-reference)

2 Answers2

Linked