Get all children recursively in Entity Framework Core

Question

In Entity Framework Core we can have recursive entities. But we cannot do an "Include" for these recursives (can we?). So I wrote a Get-method that does it for me:

First, this is my RecursiveEntity<T> base-class:

public class Entity : IEntity
{
    [Key]
    public int Id { get; set; }
}

public abstract class RecursiveEntity<TEntity> : Entity, IRecursiveEntity<TEntity> 
    where TEntity : RecursiveEntity<TEntity>
{
    public virtual TEntity Parent { get; set; }
    public virtual ICollection<TEntity> Children { get; set; }
}

And this is my Repository-base-class for recurisve entities:

public abstract class RecursiveRepository<T, TDataContext> 
    where T : RecursiveEntity<T> where TDataContext : DbContext
{
    protected IEnumerable<T> Get(Expression<Func<T, bool>> expression)
    {
        IQueryable<T> parents = DataContext.Set<T>()
            .Include(x => x.Children)
            .Where(w => w.Parent == null)
            .Where(expression);

        foreach (T entity in parents)
        {
            if (entity.Children != null && entity.Children.Any())
                entity.Children = _getChildren(entity, expression).ToList();

            yield return entity;
        }
    }

    private IEnumerable<T> _getChildren(T parentEntity, Expression<Func<T, bool>> expression)
    {
        IQueryable<T> children = DataContext.Set<T>()
            .Include(x => x.Parent)
            .Where(w => w.Parent != null && w.Parent.Id == parentEntity.Id)
            .Where(expression);

        foreach (T entity in children)
        {
            entity.Children = _getChildren(entity, expression).ToList();
            yield return entity;
        }
    }
}

Any suggestion what I can do better? Is it a good solution to do that many DataContext<Set>-accesses and assign them to the parent with ToList()?

Answer 1

If you remove virtual from Children it'll work with Include(e => e.Children):

  public abstract class RecursiveEntity<TEntity>
    : Entity, IRecursiveEntity<TEntity>
    where TEntity : RecursiveEntity<TEntity>
  {
    public virtual TEntity Parent { get; set; }
    public ICollection<TEntity> Children { get; set; }
  }

The meaning with virtual is to provide lazy loading. If you can't live with that, you'll have to do it manually, as you do.

---

Alternatively I think, you can add "MultipleActiveResultSets=True" to your connection string.

---

UPDATE

I seems that EF 6 and EFCore work differently when it comes to Include?

EFCore has the Include() and ThenInclude pattern but that is rather useless for recursive initialization.

When loading manually have you then experimented with the abilities to load navigation properties on each object as in:

public IEnumerable<TEntity> Get<TEntity>(Expression<Func<TEntity, bool>> filter) where TEntity: RecursiveEntity<TEntity>
{
  foreach (TEntity entity in Set<TEntity>().Where(e => e.Parent == null).Where(filter))
  {
    GetChildren(entity, filter);
    yield return entity;
  }
}

private void GetChildren<TEnity>(TEnity parent, Expression<Func<TEnity, bool>> childFilter) where TEnity : RecursiveEntity<TEnity>
{
  Entry(parent).Collection(e => e.Children).Query().Where(childFilter).Load();
  // Entry(parent).Reference(e => e.Parent).Load(); // I think this shouldn't be necessary because loading the children will load the parent on them

  if (parent.Children != null)
  {
    foreach (TEnity child in parent.Children)
    {
      GetChildren(child, childFilter);
    }
  }
}

It should produce the same result as yours, but is maybe a little clearer and in line with the EF concept.