Load children of children of children

Question

I have a tree of objects whose size is potentially infinite.

The entity Category has other Category entities as children. In other words, a category can have subcategories: the depth is infinite.

Here below you can see a simplified version of my entity

public class ProductCategory : IEntity<ProductCategory>, IDeletable
{
    public Guid ProductCategoryId { get; private set; }

    public virtual ICollection<ProductCategory> Children { get; set; }

    public virtual ProductCategory Father { get; set; }
}

I used to have lazy loading until some hours ago and so I was able to easily get children of children of children.

Now I have disabled lazy loading because I had problems with it, and my query to get the children of a category is the follwing.

public IEnumerable<ProductCategory> GetAllChildrenOfCategory(Guid categoryId)
{
    var results = GetAllProductCategoriesQuery()
        .Where(elt => elt.FatherId.Equals(categoryId))
        .Include(elt => elt.Father)
        .Include(elt => elt.Children);

    return results.ToList().CloneEachElement();
}

I get all the children of the category. However, I don't have the children of the children of the children...

Now I have two questions:

1. Is it possible to write a query so that you have the entire tree of categories?

2. Alternatively, is it possible to configure entity framework so that it always give you the children of category once you extract it from the database, so that I neither have to explictly include the navigation properties?

3. Other options...?

Answer 1

As far as I recall, you can use strings instead of lambda expressions in order to include properties for eager loading. It is not very elegant but it will give you a workaround:

1) Find out which is the deepest level you have, then construct a corresponding include string such as "Father.Father.Father" and use it for your includes. I cannot guarantee that it works, but I give it a fair chance.

2) In code first, I don't think there is such an option. It might exist in the EDMX, but I am not at all an expert in this. I would propose you to use an extension method to do the job:

private static readonly queryStringForMaxDepth = WriteAMethodToFindIt();

public static IQueryable<ProductCategory> DeepProductCategories(this DbContext context){
     return context.ProductCategories.Include(queryStringForMaxDepth);
}

which you will be able to use as follows:

context.DeepProductCategories()
       .Where(...)

I hope this gives you some ideas.

Answer 2

i have done this before by using view in MsSql. than i called the view directly like:

dbContext.Database.SqlQuery<ProductCategory>("select * from GetCategoryTree where RootCategoryId=1");

so this was giving me exactly what i was looking for. so it was also queryable. here is the one of the sources i used: sql recursive query parent child

and here is another sample

Someone else asked For Recursive Queries before in msdn forums

Answer 3

As a possible solution to your issue I suggest you use Explicit Loading. This way you can load the subcategories from an specific category every time that you need it:

var cat=context.FirstOrDefault(c=>c.ProductCategoryId ==categoryId);

// Load the subcategories related to a given category
context.Entry(cat).Collection(p => p.Children).Load(); 

Answer 4

The including...

FatherRepository.All().Including(x => x.Childs, x => x.Childs.Select(y => y.ChildChild));

Father class...

public class Father
{
    public int Id { get; set; }

    #region Navigations Properties
    public virtual List<Child> Childs { get; set; }
    #endregion
}

Child class...

public class Child
{
    public int Id { get; set; }
    public int ChildChildId { get; set; }
    public int FatherId { get; set; }

    #region Navigations Properties
    public virtual Father Father { get; set; }
    public virtual ChildChild ChildChild { get; set; }
    #endregion

}

ChildChild class...

public class ChildChild
{
    public int Id { get; set; }
}