How to loop through a child list of objects in Entity Framework 4.1 code first

I am using Entity Framework 4.1 code first.

Here is my Category class:

public class Category
{
     public int Id { get; set; }
     public string Name { get; set; }
     public bool IsActive { get; set; }
     public int? ParentCategoryId { get; set; }
     public virtual Category ParentCategory { get; set; }
     public virtual ICollection<Category> ChildCategories { get; set; }
}

The above class is a self-referencing category, for example, a parent category can have a list of child categories.

I want to create a string value of the parent category name and the child category name, for example, Parent Category 1 > Child Category 1-1.

So I get a list of all the parent categories, loop through each parent category. And for each parent category I want to loop through the child category list and combine each child category's name to the parent category's name, so that I have something like:

Animal > Lion
Anumal > Baboon
Anumal > Zebra
etc etc etc...

Here is my looping code. If anyone can help me reduce the lines of code then I would appreciate it :)

public IEnumerable<Category> GetParentChildCategories()
{
     IEnumerable<Category> parentCategoryList = GetParentCategories()
          .Where(x => x.IsActive);
     List<Category> parentChildCategoryList = new List<Category>();

     foreach (Category parentCategory in parentCategoryList)
     {
          foreach (Category childCategory in parentCategory.ChildCategories)
          {
               if (childCategory.IsActive)
               {
                    Category category = new Category
                    {
                         Id = childCategory.Id,
                         Name = parentCategory.Name + " > " + childCategory.Name
                    };
                    parentChildCategoryList.Add(category);
               开发者_高级运维}
          }
     }

     return parentChildCategoryList;
}

It bombs out in the 2nd foreach when wanting to loop through the child categories. Why is is this? Here is the error:

There is already an open DataReader associated with this Command which must be closed first.

EF opens a reader when you iterate parentCategoryList. Then again when you try to iterate parentCategory.ChildCategories EF will open a Reader. Since there is open reader it will throw an error.

What you should do is eager load the ChildCategories. This way EF does not have to open a reader again.

So inside your GetParentCategories() method, use Include to eager load them

return db.Categories.Include(c => c.ChildCategories).Where(/* */);

add

MultipleActiveResultSets=True

in the connection string

If you just want the combination to be Parent->Child (Category Name) why not return it through a property and no need for that heavy work

Do a partial class for the Category class then write the following property

public string MeAndMyParentCategory
{
   get
   {
      //I assuming that your 
      // (Child's relation with the parent category called [Parent])
      if(this.Parent != null)
         return string.Format("{0} > {1}", Parent.Name, this.Name);
      return string.Empty
   }
}