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