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.

Answer 1

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.