Eager load a self-referencing table

Question

I have a standard self referencing table of Categories. In my entity model I have made associations Children and Parent. Is it possible to load the whole Category object without lazy loading?

if I use the code below, it loads only to the second level.

db.Categories.MergeOption = System.Data.Objects.MergeOption.NoTracking;

var query = from c in db.Categories.Include("Children")
            where c.IsVisible == true
            orderby c.SortOrder, c.Id
            select c;

Is it possible to load references if I have all the category objects already loaded?

One method to load it is to add the Children property multiple times

db.Categories.Include("Children.Children.Children.Children.Children")

but this generates a very long insane T-SQL code and also it doesn't do what I want.

Answer 1

No, it isn't possible. Consider: All LINQ to Entities queries are translated into SQL. Which SQL statement includes an unlimited depth in a self-referencing hierarchy? In standard SQL, there isn't one. If there's an extension for this in T-SQL, I don't know what it is, and I don't think EF providers do, either.

Answer 2

Ok, you might consider using Load method.

 if (!category.Children.IsLoaded)
        category.Children.Load();

Of course, category entity need to be tracked by ObjectContext.

There is better explanation here how-does-entity-framework-work-with-recursive-hierarchies-include-seems-not-to.