How to do recursive load with Entity framework?

When you use Include(), you are asking the Entity Framework to translate your query into SQL. So think: How would you write an SQL statement which returns a tree of an arbitrary depth?

Answer: Unless you are using specific hierarchy features of your database server (which are not SQL standard, but supported by some servers, such as SQL Server 2008, though not by its Entity Framework provider), you wouldn't. The usual way to handle trees of arbitrary depth in SQL is to use the nested sets model rather than the parent ID model.

Therefore, there are three ways which you can use to solve this problem:

1. Use the nested sets model. This requires changing your metadata.
2. Use SQL Server's hierarchy features, and hack the Entity Framework into understanding them (tricky, but this technique might work). Again, you'll need to change your metadata.i
3. Use explicit loading or EF 4's lazy loading instead of eager loading. This will result in many database queries instead of one.

I wrote something recently that does N+1 selects to load the whole tree, where N is the number of levels of your deepest path in the source object.

This is what I did, given the following self-referencing class

public class SomeEntity 
{
  public int Id { get; set; }
  public int? ParentId { get; set; }
  public string Name { get; set;
}

I wrote the following DbSet helper

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Threading.Tasks;

namespace Microsoft.EntityFrameworkCore
{
    public static class DbSetExtensions
    {
        public static async Task<TEntity[]> FindRecursiveAsync<TEntity, TKey>(
            this DbSet<TEntity> source,
            Expression<Func<TEntity, bool>> rootSelector,
            Func<TEntity, TKey> getEntityKey,
            Func<TEntity, TKey> getChildKeyToParent)
            where TEntity: class
        {
            // Keeps a track of already processed, so as not to invoke
            // an infinte recursion
            var alreadyProcessed = new HashSet<TKey>();

            TEntity[] result = await source.Where(rootSelector).ToArrayAsync();

            TEntity[] currentRoots = result;
            while (currentRoots.Length > 0)
            {
                TKey[] currentParentKeys = currentRoots.Select(getEntityKey).Except(alreadyProcessed).ToArray();
                alreadyProcessed.AddRange(currentParentKeys);

                Expression<Func<TEntity, bool>> childPredicate = x => currentParentKeys.Contains(getChildKeyToParent(x));
                currentRoots = await source.Where(childPredicate).ToArrayAsync();
            }

            return result;
        }
    }
}

Whenever you need to load a whole tree you simply call this method, passing in three things

1. The selection criteria for your root objects
2. How to get the property for the primary key of the object (SomeEntity.Id)
3. How to get the child's property that refers to its parent (SomeEntity.ParentId)

For example

SomeEntity[] myEntities = await DataContext.SomeEntity.FindRecursiveAsync(
  rootSelector: x => x.Id = 42,
  getEntityKey: x => x.Id,
  getChildKeyToParent: x => x.ParentId).ToArrayAsync();
);

Alternatively, if you can add a RootId column to the table then for each non-root entry you can set this column to the ID of the root of the tree. Then you can fetch everything with a single select

DataContext.SomeEntity.Where(x => x.Id == rootId || x.RootId == rootId)

This is an old question, but the other answers either had n+1 database hits or their models were conducive to bottom-up (trunk to leaves) approaches. In this scenario, a tag list is loaded as a tree, and a tag can have multiple parents. The approach I use only has two database hits: the first to get the tags for the selected articles, then another that eager loads a join table. Thus, this uses a top-down (leaves to trunk) approach; if your join table is large or if the result cannot really be cached for reuse, then eager loading the whole thing starts to show the tradeoffs with this approach.

To begin, I initialize two HashSets: one to hold the root nodes (the resultset), and another to keep a reference to each node that has been "hit."

var roots = new HashSet<AncestralTagDto>(); //no parents
var allTags = new HashSet<AncestralTagDto>();

Next, I grab all of the leaves that the client requested, placing them into an object that holds a collection of children (but that collection will remain empty after this step).

var startingTags = await _dataContext.ArticlesTags
        .Include(p => p.Tag.Parents)
        .Where(t => t.Article.CategoryId == categoryId)
        .GroupBy(t => t.Tag)
        .ToListAsync()
        .ContinueWith(resultTask => 
             resultTask.Result.Select(
                  grouping => new AncestralTagDto(
                        grouping.Key.Id, 
                        grouping.Key.Name)));

Now, let's grab the tag self-join table, and load it all into memory:

var tagRelations = await _dataContext.TagsTags.Include(p => p.ParentTag).ToListAsync();

Now, for each tag in startingTags, add that tag to the allTags collection, then travel down the tree to get the ancestors recursively:

foreach (var tag in startingTags)
{
    allTags.Add(tag);
    GetParents(tag);
}
return roots;

Lastly, here's the nested recursive method that builds the tree:

void GetParents(AncestralTagDto tag)
{
    var parents = tagRelations.Where(c => c.ChildTagId == tag.Id).Select(p => p.ParentTag);
    if (parents.Any()) //then it's not a root tag; keep climbing down
    {
        foreach (var parent in parents)
        {
            //have we already seen this parent tag before? If not, instantiate the dto.
            var parentDto = allTags.SingleOrDefault(i => i.Id == parent.Id);
            if (parentDto is null)
            {
                parentDto = new AncestralTagDto(parent.Id, parent.Name);
                allTags.Add(parentDto);
            }

            parentDto.Children.Add(tag);
            GetParents(parentDto);
        }
    }
    else //the tag is a root tag, and should be in the root collection. If it's not in there, add it.
    {
        //this block could be simplified to just roots.Add(tag), but it's left this way for other logic.
        var existingRoot = roots.SingleOrDefault(i => i.Equals(tag));
        if (existingRoot is null)
            roots.Add(tag);
    }
}

Under the covers, I am relying on the properties of a HashSet to prevent duplicates. To that end, it's important that the intermediate object that you use (I used AncestralTagDto here, and its Children collection is also a HashSet), override the Equals and GetHashCode methods as appropriate for your use-case.