GroupJoin produces very surprising result

Question

I have a database table like shown below:

MenuItemID       |    int   [Primary Key]   1 -----+
MenuItemName     |    nvarchar(100)                |
ParentID         |    int                   * -----+

As you can see in the above schematic diagram, ParentID column refers to MenuItemID due to which we can obtain hierarchical outpu.

The sample data in above table is as shown below:

From the sample data above, I want the output like:

Electronics
    Mobile
    Desktop
    Laptop
        Lenovo
        Dell
Sports
    Cricket
    Football
    Hockey
Stationary
    Books
    Pens
    Pencils
    Erasers

What I have tried:

I have tried the below mentioned code, where I tried to use GroupJoin extension method to achieve the required output:

class Program
{
    static void Main(string[] args)
    {
        OnlineShoppingEntities db = new OnlineShoppingEntities();

        var x = db.MenuItems.GroupJoin(db.MenuItems,
                                       m => m.MenuItemID,
                                       m => m.ParentID,
                                       (parentMenuItems, childMenuItems) => new
                                       {
                                           ParentMenuItems = parentMenuItems,
                                           ChildMenuItems = childMenuItems
                                       });

        foreach (var v in x)
        {
            Console.WriteLine(v.ParentMenuItems.MenuItemName);
            foreach (var m in v.ChildMenuItems)
            {
                Console.WriteLine("\t" + m.MenuItemName);
            }
        }
    }
}

The output I got:

I am very much surprised with the output, as I expected the required output as mentioned above.

Answer 1

Of course, if your data have only 3 levels you can do it with other GroupJoin() for simplicity:

var hierarchical = menuItems.Where(m => m.ParentID == null)
                                .GroupJoin(menuItems,
                                            m => m.MenuItemID, 
                                            m => m.ParentID,
                                            (parentMenuItems, childMenuItems) => new
                                            {
                                                ParentMenuItems = parentMenuItems,
                                                ChildMenuItems = childMenuItems.GroupJoin(menuItems,
                                                m => m.MenuItemID, 
                                                m => m.ParentID,
                                                (subParentMenuItems, subChildMenuItems) => new
                                                {
                                                    ParentMenuItems = subParentMenuItems,
                                                    ChildMenuItems = subChildMenuItems
                                                })
                                            });

    foreach(var menu in hierarchical)
    {
        Console.WriteLine(menu.ParentMenuItems.MenuItemName);
        foreach(var submenu in menu.ChildMenuItems)
        {
            Console.WriteLine("\t" + submenu.ParentMenuItems.MenuItemName);
            foreach(var subitem in submenu.ChildMenuItems)
            {
                Console.WriteLine("\t\t" + subitem.MenuItemName);
            }
        }
    }

Answer 2

Your solution can be found here at SO: Hierarchical data in Linq - options and performance.

I won't abstract the code for there are so many answers there that can solve your problem, by I specially like the AsHierarchy() linq extension method as shown on Kyle's answer to that question.

var hierachy = dc.Employees.ToList().AsHierarchy(e => e.EmployeeID, e => e.ReportsTo);

The code for the extension by Stefan Cruysberghs is:

using System;
using System.Collections.Generic;
using System.Linq;

namespace ScipBe.Common.LinqExtensions
{
  // Stefan Cruysberghs, http://www.scip.be, March 2008

  /// <summary>
  /// Hierarchy node class which contains a nested collection of hierarchy nodes
  /// </summary>
  /// <typeparam name="T">Entity</typeparam>
  public class HierarchyNode<T> where T : class
  {
    public T Entity { get; set; }
    public IEnumerable<HierarchyNode<T>> ChildNodes { get; set; }
    public int Depth { get; set; }
  }

  public static class LinqExtensionMethods
  {
    private static System.Collections.Generic.IEnumerable<HierarchyNode<TEntity>> CreateHierarchy<TEntity, TProperty>
      (IEnumerable<TEntity> allItems, TEntity parentItem, 
      Func<TEntity, TProperty> idProperty, Func<TEntity, TProperty> parentIdProperty, int depth) where TEntity : class
    { 
      IEnumerable<TEntity> childs;

      if (parentItem == null)
        childs = allItems.Where(i => parentIdProperty(i).Equals(default(TProperty)));
      else
        childs = allItems.Where(i => parentIdProperty(i).Equals(idProperty(parentItem)));

      if (childs.Count() > 0)
      {
        depth++;

        foreach (var item in childs)
          yield return new HierarchyNode<TEntity>() { Entity = item, ChildNodes = CreateHierarchy<TEntity, TProperty>
            (allItems, item, idProperty, parentIdProperty, depth), Depth = depth };
      }
    }

    /// <summary>
    /// LINQ IEnumerable AsHierachy() extension method
    /// </summary>
    /// <typeparam name="TEntity">Entity class</typeparam>
    /// <typeparam name="TProperty">Property of entity class</typeparam>
    /// <param name="allItems">Flat collection of entities</param>
    /// <param name="idProperty">Reference to Id/Key of entity</param>
    /// <param name="parentIdProperty">Reference to parent Id/Key</param>
    /// <returns>Hierarchical structure of entities</returns>
    public static System.Collections.Generic.IEnumerable<HierarchyNode<TEntity>> AsHierarchy<TEntity, TProperty>
      (this IEnumerable<TEntity> allItems, Func<TEntity, TProperty> idProperty, Func<TEntity, TProperty> parentIdProperty)
      where TEntity : class
    {
      return CreateHierarchy(allItems, default(TEntity), idProperty, parentIdProperty, 0);
    }
  }
}