select the all related entity with the only linq query

Question

I have Category table that has a relation on itself. That is it's a tree hierarchical view, for example: oldfather_category -> father_category -> child_category.

So I need to build tree view on this table. I need the all rows from it. At first i do this:

    IEnumerable<Category> list = _service.Get().Where(c => c.ParentID == null);
    Node(list);

When I use the recurcive like this:

    private void Node(IEnumerable<Category> list)
    {
        foreach (Category c in list)
        {
            //if has childs
            if (c.Category1.Count > 0)
                Node(c.Category1);
        }
    }

entity framework builds select every time when I call c.Category1.Count property (Deferred Execution magic). But I want to load the all entities in first statement, I want make the only select to sql server. How can I do that? I hope the question is clear)

Edited: when I use ToList() the Category1 property is null whatever

Answer 1

It's not possible to load all of the elements in a tree without writing a function on the db server to loop through all the children.

Depending on the actual query you need to run however, you might be able to hack up something that works. If specific elements will always be the root of any tree, you can add a third reference on each element to the root of the tree and then build the actual tree in memory. For instance, in the case of comments on a blog, the blog post id might always be the root of the tree, and then each comment still has the parent to which it is a response to. Add a root identifier to each element and query that, then build the tree recursively in memory.

If the root for your query is not always such a predefined type of element, you'll be better off writing a function for your server to handle this situation. You'll lose the ability to use entity framework for this query however.