EF Core: Counting the Number of Multi-Level Children of an Entity

Question

I have the following entity:

public class Entity {
    Guid Id { get; set; }
    Guid? ParentId { get; set; }
    virtual Entity Parent { get; set; }
    virtual ICollection<Entity> Children { get; set; }
}

Given a certain entity id: theId, I wish to count the number of its children, its children's children, its children's children's children, etc..

One naive way to do it is as follows:

var count = dbContext.Where(x => x.Id == theId)
    .SelectMany(x => x.Children)
    .SelectMany(x => x.Children)
    .SelectMany(x => x.Children)
    .SelectMany(x => x.Children)
    .SelectMany(x => x.Children)
    .Count();

The problem is that I need to increase the number SelectManys to the max depth of children I have, which is not practical. I feel like this is not possible using EntityFramework and I have to use SQL procedures, but I wanted to ask just to make sure of this.

P.S. I'm using EF Core.

that would be difficult, as you will need to do a [recursion](https://www.urbandictionary.com/define.php?term=recursion). i am not aware trivial way to do recursion on entity framework. you have to load the entire rows into memory and walks from there as shown in [this answer](https://stackoverflow.com/a/1354734/4648586) and [this answer](https://stackoverflow.com/questions/52138605/c-sharp-entity-framework-recursive-hierarchy-query), and [this](https://stackoverflow.com/questions/40987365/implementing-recursive-property-loading-in-ef-core). — Bagus Tesa, Sep 30 '19 at 01:18
@BagusTesa There are a couple of techniques to achieve this, the issue however is that I want to load many entities ids and their respective counts. I'd like to be able to do that using lowest number of queries. — boring91, Sep 30 '19 at 05:05
Best strategy I know is using left and right values, like this place here:https://www.sitepoint.com/hierarchical-data-database-2/ you have to write custom queries checking for left and right values because there is no way that EF can do anything by magic in a hieracy — Thomas Koelle, Sep 30 '19 at 11:36
@ThomasKoelle Thanks, I had a look at it, it's an interesting approach, though it would be challenging if the hierarchy needs to be updated. I posted a solution to this, that have, I guess, less overhead when dealing with hierarchy updates. — boring91, Sep 30 '19 at 23:14

boring91 · Accepted Answer · 2019-09-30T23:42:52.597

I worked out a solution that works for my case, here it goes: I added another property to Entity; I called it Hierarchy. Its value can be 000, 001, 002, ..., 00y, 00z, 010, ..., zzy, ..., zzz. In other words, it is a 3-digit base-62 number (but stored as a string). Additionally, the Hierarchy of a child entity will be prefixed with the Hierarchy of its parent. So for example if we have a parent entity ParentEntity with Hierarchy of 001, which has a child entity ChildEntity with Hierarchy of 000, the child entity's final value for Hierarchy would be: 001000. This way, if I want to get a parent's children in all levels, I would just do this:

dbContext.Entities.Count(x => x.Hierarchy.StartsWith(parentEntity.Hierarchy))

The limitation to this solution is the limited number of children for each parent, in this case it is 62*62*62 = 238,328. This number is more than enough for my case, but can be increased by increasing the size of the Hierarchy field, e.g., 4-digit as opposed to 3-digit number.

Of course you'd have to be careful when updating the entities; whenever an entity's level has changed, update the Hierarchy accordingly. Also, I created an index for the Hierarchy field in the database for faster search.

EF Core: Counting the Number of Multi-Level Children of an Entity

1 Answers1