LINQ to SQL - Grouping categories by parentId

I am trying to construct a navigation menu using a Categories table from my db.

I have a similar layout as below in Categories table.

public List<Category> CategoryData = new List(new Category[] {  
                                        new Category{ CategoryId = 1, Name = "Fruit", ParentCategoryId = null},
                                        new Category{ CategoryId = 2, Name = "Vegetables", ParentCategoryId = null},
                                        new Category{ CategoryId = 3, Name = "Apples", ParentCategoryId = 1},
       开发者_如何学运维                                 new Category{ CategoryId = 4, Name = "Bananas", ParentCategoryId = 1},
                                        new Category{ CategoryId = 5, Name = "Cucumber", ParentCategoryId = 2},
                                        new Category{ CategoryId = 6, Name = "Onions", ParentCategoryId = 2}
                                );  }

The above should return something like

Fruit (parent)

 "===Apples, Bananas (child)

Vegetables (parent)

"===Cucumber, Onions (child)

I need to be able to pass this as some kind of 'grouped' (grouped by parentid) collection to my View.

How to do this?

---

It would seem like this is a good example of when translating your model to a viewModel would come in handy. As you could create a collection of CategoryViewModel which have a Childrens property of CategoryViewModel using the same technique describe by @thomas.

public class CategoryViewModel
{
     public int CategoryId { set; get; }
     public string CategoryName { set; get; }
     public int? ParentCategoryId { set; get; }
     public IEnumerable<CategoryViewModel> Children { set; set; }
}

public static IEnumerable<CategoryViewModel> GetAllCategoryViewModel(IList<Category> categories) 
{
      var query = GetChildren(null, categories);
      return query.ToList();

}

public static IEnumerable<CategoryViewModel> GetChildren(int? parentId, IList<Category> categories)
{
     var children = from category in categories
                    where category.ParentCategoryId == parentId
                    select  
                     new CategoryViewModel
                     {
                       CategoryId = category.CategoryId,
                       CategoryName = category.CategoryName,
                       ParentCategoryId = category.ParentCategoryId,
                       Children = GetChildren(category.CategoryId, categories)
                     };

     return children;
}

---

How about something like:

private void Test()
{
    var categoryData = new List
                                      {
                                          new Category {CategoryId = 1, Name = "Fruit", ParentCategoryId = null},
                                          new Category {CategoryId = 2, Name = "Vegetables", ParentCategoryId = null},
                                          new Category {CategoryId = 3, Name = "Apples", ParentCategoryId = 1},
                                          new Category {CategoryId = 4, Name = "Bananas", ParentCategoryId = 1},
                                          new Category {CategoryId = 5, Name = "Cucumber", ParentCategoryId = 2},
                                          new Category {CategoryId = 6, Name = "Onions", ParentCategoryId = 2}
                                      };
    var query = from category in categoryData
                where category.ParentCategoryId == null
                select category;

    foreach ( var item in query )
    {
        Debug.WriteLine( string.Format( "{0} (parent)", item.Name ) );
        Debug.WriteLine( GetChildren(item.CategoryId, categoryData  ) );
    }

}
private static string GetChildren( int parentCategoryId, IEnumerable categoryData)
{
    var children = ( from category in categoryData
                     where category.ParentCategoryId == parentCategoryId
                     select category.Name ).ToArray();

    return string.Format( "==={0} (child)", string.Join( ", ", children ) );
}

---

var list = from a in CategoryData
        join b in CategoryData on a.ParentCategoryId equals b.CategoryId into c
        from d in c.DefaultIfEmpty()
        where d != null
        select new {
            a.CategoryId,
            a.Name,
            a.ParentCategoryId,
            ParentName = d.Name
        };

returns

CategoryId  Name      ParentCategoryId  ParentName
3             Apples      1                 Fruit
4             Bananas     1                 Fruit
5             Cucumber    2                 Vegetables
6             Onions      2                 Vegetables

You can then loop through it in your view and format accordingly.