Entity Framework Recursive Relationship Hierarchical Data

Question

Does Entity Framework support some kind of recursive LINQ or do I have to write my queries in SQL? (Using ParentId - Typical category subcategory problem)

@cloudikka basic operations like for example taking out the whole hierarchy — john, Dec 24 '17 at 11:14
(1) It doesn't. It's not EF specific, but `Queryable` and expression tree limitation in general - no way to define recursive expression. (2) Taking the whole hierarchy is possible due the EF navigation property fixup. But taking a part of the tree with all children is not possible with single L2E query (except if you load the whole three and then apply the filter on memory). See [Is recursive query possible in LINQ](https://stackoverflow.com/questions/41894751/is-recursive-query-possible-in-linq/41909322#41909322) — Ivan Stoev, Dec 24 '17 at 11:22
@IvanStoev: It is not exactly true expression trees have such a limitation. You can write recursive expression. For example [Factorial expression](https://chriscavanagh.wordpress.com/2012/06/18/recursive-methods-in-expression-trees/). — dropoutcoder, Dec 24 '17 at 11:51
@cloudikka But it's using `Finc<..>` inside. I meant pure expressions (which are requirement in queryables because they have to be recognizable and translatable, which cannot be said for unknown `Func<...>` code) — Ivan Stoev, Dec 24 '17 at 12:02
@IvanStoev: I am afraid you don't know what you are talking about. Expression> and Expression> is used all around the LINQ world. The problem is writing dynamic expression tree to work with SQL translation is not trivial and you need to have deep understanding what is happening under the hood. — dropoutcoder, Dec 24 '17 at 12:11
@john: Please, update your question with code and list of what exactly at this point you need to achieve. Thanks. — dropoutcoder, Dec 24 '17 at 12:12
@IvanStoev Oh, you were talking about translation of the MakeFactorialExpression method. That's true it can't be translated by default. — dropoutcoder, Dec 24 '17 at 13:08

score 0 · Answer 1 · answered Dec 24 '17 at 15:27

I think you can solve it by using Include method inside extension method to get only piece of hierarchy or get it all. Even it can generate pretty ugly SQL.

using(var context = new HierarchyContext())
{
    var depth = context
                .Categories
                .IncludeHierarchy(3, nameof(Category.Children));

    var root = depth.Single(c => c.Id == 2);
}


public static IQueryable<T> IncludeHierarchy<T>(this IQueryable<T> source, 
uint depth, string propertyName)
where T : Category
{
    var temp = source;

    for (var i = 1; i <= depth; i++)
    {
        var sb = new StringBuilder();

        for (var j = 0; j < i; j++)
        {
            if (j > 0)
            {
                sb.Append(".");
            }

            sb.Append(propertyName);
        }

        var path = sb.ToString();

        temp = temp.Include(path);
    }

    var result = temp;

    return result;
}

public class Category
{    
   // Primary key    
   public int Id { get; set; }

   // Category name    
   public string Name { get; set; }

   // Foreign key relationship to parent category    
   public int ParentId { get; set; }

   // Navigation property to parent category    
   public virtual Category Parent { get; set; }

   // Navigation property to child categories    
   public virtual ICollection<Category> Children { get; set; }    
}

Entity Framework Recursive Relationship Hierarchical Data

1 Answers1