Efcore: Support for querying a hierarchy of self-referencing entities?

[weitzhandler]

Hi,

Often you need to retrieve a whole hierarchy of self-referencing entities.

Consider this example:

public class Affiliate
{
  public int Id { get; set; }
  public string Name { get; set; }
  //The person who referred the current one
  public in ReferrerId { get; set; }
  [ForeignKey(nameof(ReferrerId)]
  public Affiliate Referrer { get; set; }
  //People the current has referred
  public virtual ICollection<Affiliate> Referred { get; set; } = new HashSet<Affiliate>();
}

Now I want to retrieve an Affiliate along with its children, grandchildren, and great grandchildren. Or I want to retrieve an Affiliate with its Referrer, he's referrer's referrer, and his referrer's referrer's referrer n levels up.

How can this be achieved in a single round-trip without involving stored-procedures?

• Can Linq Expressions be used for this?
• Is there a repo for that?
• Anything for EF6?

[maumar]

Your best bet would be:
context.Affiliates.Include(a => a.Referred).ThenInclude(a => a.Referred)

This will pull affiliates along with their children and grandchildren.

For one-to-one navigations you can use:
context.Affiliates.Include(a => a.Referrer.Referrer)

This will pull patent and grandparent. The second case will be done with one query to the database. In case of one-to-many there will be multiple queries, one per include level.

[maumar]

In EF6 you would do a similar thing:
context.Affiliates.Include("Referred.Referred")

There is no way in either EF6 or EF7 to automatically retrieve the entire hierarchy.

[weitzhandler]

@maumar thanks. that's awesome. this way I can even do this recursively.

i was only wondering if Include is queryable i.e. Include Where.

[maumar]

@weitzhandler yes, you can compose on top of Include like this:

context.Affiliates.Include(a => a.Referred).Where(a => a.Id >5),

but you can't so stuff like:
context.Affiliates.Include(a => a.Referred.Where(r => r.Id >5))

[weitzhandler]

I hope that will be implemented too.

Anyway it can be achieved if the selection is on the Referred entity context.Affiliates.Include(a => a.Referred).Select(a => a.Referred).Where(r => r.Id > 5)

However, I'm still not sure how to achieve recursive inclusion of self-referential entities:

I want to achieve Affiliates.Include(a => a.Referred.Referred.Referred.Referred), in a dynamic way, basically I want to implement this function

Affiliates.Include<TEntity>(int levelIndex, Expression<Func<TEntity, TEntity>> expression)

which returns processes the base query and processes the base expression to add a .Referrer to the original expression, upon each level.

I've done it the ugly hardcoded way, but should work (didn't test yet):

public static IQueryable<TEntity> Include<TEntity>(this IQueryable<TEntity> source,
  int levelIndex, Expression<Func<TEntity, TEntity>> expression)
{
  if (levelIndex < 0)
    throw new ArgumentOutOfRangeException(nameof(levelIndex));
  var member = (MemberExpression)expression.Body;
  var property = member.Member.Name;
  var sb = new StringBuilder();
  for (int i = 0; i < levelIndex; i++)
  {
    if (i > 0)
      sb.Append(Type.Delimiter);
    sb.Append(property);
  }
  return source.Include(sb.ToString());
}

See this forum. There is a demand for this. I really hope it gets in to the EF.

[maumar]

@weitzhandler currently, the Include(string) is supposed to be used for dynamic include generation, just like you did in the example above. Another way is to use ThenInclude() repeatedly for each consecutive level on navigation property (this can be done on both 1-1 and 1-many), e.g.:

Affiliates.Include(a => a.Referred).ThenInclude(a => a.Referred).ThenInclude(...)

Alternatively one could hand-craft a lambda expression and pass it to the Include, although that is arguably more messy than using any of the above methods.

Finally, if you are able to determine all the entities that are part of the hierarchy you need (e.g if you are using hierarchyid), you can just query for those, and EF will hook up references between them automatically.

[weitzhandler]

@maumar Yeah, I initially tried to achieve this with expressions, but once I found the string version I decided to got for it...
Anyway, couldn't find ThenInclude, is it EF≥7 specific?

Appreciate your responses!

[maumar]

@weitzhandler yes, ThenInclude if EF7 API, mainly to allow chaining includes on collections in a strongly typed fashion.

[weitzhandler]

@maumar oh, so you sayin that in EF6 it's only possible out-the-box with the hardcoded version?

[maumar]

@weitzhandler correct, you need to hand-craft either a string or a lambda expression

[weitzhandler]

@maumar Thanks for all the info!

[weitzhandler]

The problem begins when you want to select children instead of parents, or you want to select a predefined view of children, i.e. all children in level 3 and 4, along with their phones.
This issue might be related

[alvaromongon]

Hi guys, just for you to know,

I am using EF6 and have a hierarchy structure that need to load in some special case. At first I tried to load the full hierarchy using the "Include(string)" building the string with a loop like weitzhandler sugested. But then I decided to just load the sub-elements using the load method.

It ends up being much quicker with the load method, probably because I am just asking for the one more level that I really need for each of the tree nodes in the hierarchy. But using a string, I might ask for much more that I really need. On the other hand, the string does not ensure to load the full hierarchy.

Still I understand that it might be dangerous to load the full hierarchy and should only be used in controlled cases.

This the the code I wrote (owner is the father of a hierarchy of groups and sub-groups):
`
Owner owner = await Set.Where(o => o.Key == key)
.Include(o => o.Groups)
.FirstOrDefaultAsync();

foreach(Group group in owner.Groups)
{
    LoadGroups(group);
}

return owner;

}

private void LoadGroups(Group group)
{
_dataContext.Entry(group).Collection(c => c.Groups).Load();

foreach (Group subGroup in group.Groups)
{
    LoadGroups(subGroup);
}

}
`

[mdczaplicki]

@alvaromongon this actually helped me, you just need to be careful not to create a loop in db.
I have made these methods in my UnitOfWork:

public void LoadAllSubordinates(ref User user)
{
    this.LoadSubordinates(user);
}

private void LoadSubordinates(User user)
{
    this.context.Entry(user).Collection(u => u.DirectSubordinates).Load();
    foreach (var userDirectSubordinate in user.DirectSubordinates)
    {
        this.LoadSubordinates(userDirectSubordinate);
    }
}

[lakeman]

A trick I've used before is to maintain another table with a [parent, depth, sort, child] record for every relationship between ancestors and descendants. Then you can query this table to either return all parents sorted by depth, or all children sorted in a appropriate order to display in a tree structure.

[sophisma]

I know this thread is old but it pointed me in the right direction.
I'm just posting this in case it helps other people.
I had an issue where I had an hierarchy of categories of products, for instance, Storage->Internal Storage->SSD, and I had to retrieve the entire hierarchy from a child.
The examples posted here were recursive, which can take a performance hit.
You can do it iteratively this way:

        public Category GetById(int id)
        {
            var category = this.ObjectSet.FirstOrDefault(e => e.Id == id);

            this.IncludeParentCategories(category);

            return category;
        }

        private void IncludeParentCategories(Category category)
        {
            var currentCategory = category;

            do
            {
                this.UnitOfWork.Context.Entry(currentCategory).Reference(e => e.ParentCategory).Load();
                currentCategory = currentCategory.ParentCategory;
            }
            while (currentCategory != null);
        }

[dimkoug]

How to calculate the depth of each level of the tree ?

[sophisma]

In this case you're walking backwards in the hierachy when you are loading it, so it's not possible to know the depth during this phase.
You would need a second method that, given a category, you would "walk" back to his parent and do a breadth-first traversal of the tree. You would need to keep track of the level while you enqueue child categories to be traversed.
Check this link:
https://www.cs.bu.edu/teaching/c/tree/breadth-first/
Hope this puts you in the right direction.