Entity framework: eager load whole self-referencing table

Question

Hi, i have standart self-referencing table of categories. In entity model i have made association "Children" and "Parent". It is possible to load whole Category object without lazy loading?

if i use the code bellow, it's load only to second level (depth).

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;

It is possible to load references if i have all the category objects allready loaded?

One method to load it is add children property multipletimes

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

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

Answer 1

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.

Answer 2

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.