EF4.1 - recursive query to get hierarchical data

Question

I come up with this:

public partial class Forum
{
    public List<Forum> GetHierachy(Forum foru)
    {
        foreach (var forum in foru.SubForums.ToList())
        {
            yield return GetHierachy(forum);
        }
    }
}

For this:

public partial class Forum
{
    public int Id { get; set; }
    public int SubForumId { get; set; }

    public virtual ICollection<Forum> SubForums { get; set; }
    public virtual Forum ParentForum { get; set; }
}

And I get that:

The body of 'Jami.Data.Forum.GetHierachy(Jami.Data.Forum)' cannot be an iterator block because 'System.Collections.Generic.List<Jami.Data.Forum>' is not an iterator interface type:

Then I found out this: Some help understanding "yield"

So I changed my method to:

public IEnumerable<Forum> GetHierachy(Forum foru)
    {
        foreach (var forum in foru.SubForums.ToList())
        {
            yield return GetHierachy(forum);
        }
    }

And Now I'm getting missing cast exception.

Cannot implicitly convert type 'System.Collections.Generic.IEnumerable<Jami.Data.Forum>' to 'Jami.Data.Forum'. An explicit conversion exists (are you missing a cast?)

A this point I have no slighest idea why this exception occurs. I might be wrong, but for me it looks like returning collection not single item.

Read [yield (C# Reference)](http://msdn.microsoft.com/library/9k7k7cf0.aspx). — Bertrand Marron, Jun 26 '11 at 12:36
Yes it returns collection because it returns return type of your `GetHierarchy`. Iterators do not nest. — Ladislav Mrnka, Jun 26 '11 at 13:26

score 4 · Accepted Answer · answered Jun 26 '11 at 13:31

Yes it returns collection because it returns return type of your GetHierarchy. Iterators do not nest. You need something like:

public IEnumerable<Forum> GetHierachy(Forum forum)
{
    yield forum;

    foreach (var x in forum.SubForums.SelectMany(s => GetHierarchy(s)))
    {
        yield return x;
    }
}

When I look at this query I definitely don't like it. It is ugly and it will perform terribly bad because this will use lazy loading to load data = a lot of queries to database. Hierarchical queries should be done directly in database with Common table expressions.

score 0 · Answer 2 · answered Oct 06 '11 at 21:55

0

Have a look at this excellent solution to query hierarchical data

It may require some db alterations but its worth it.

answered Oct 06 '11 at 21:55

ballyshan

1
1

1

This solution is not viable if you do not intend to tie yourself down to one implementation of database and only supports trees, what about graphs (many-to-many) hierarchy? – Tri Q Tran Jun 14 '12 at 01:31

EF4.1 - recursive query to get hierarchical data

2 Answers2