Asp.net MVC - LINQ and Hieararchical data

Question

I have a database with this two tables

ProductCategory    
 IDCat
 Name
 Description


ProductSubCategories
 IDSub
 Name
 IDCat

This tables are related trought IDCat.

How can i do with LINQ to retrieve the list of Category and SubCategory grouped by Category ??

In my PartialView I would like create a menu with Category and SubCategories like this :

Category1
    SubCategory1
    SubCategory2
    SubCategory3
 Category2
    SubCategory1
    SubCategory2
    SubCategory3
 Category3
    SubCategory1

Answer 1

MyDataContext db=new MyDataContext();

IQueryable<ProductCategory> categories= db.Categories;

foreach (Category c in categories){
  Console.WriteLine (c.Name);
  foreach (ProductSubCategories sub in c.ProductSubCategories){
    Console.Writeline (sub.Name);
  }  
}

Edit to put answer in View Format

<% foreach (Category c in Model){ %>
<%= Html.Encode(c.Name) %> <br />
    <% foreach (ProductSubCategories sub in Model.Categories){ %>        
        <%= Html.Encode(sub.Name) %> <br />               
    <% } %>
<%} %>

This should work (or be close), but I have note tested syntax. Note that as others have indicated, your primary and foriegn keys must be set up correctly for this to work.

Answer 2

If you set up your PK-FK correctly in DB, LINQ to SQL will do the object association for you. So there will be a property in ProductCategory as an EntitySet of ProductSubCategory.

The key is that you DB PK-FK ref must be correct for the LINQ code generator to pick this up.

Answer 3

   MyDataContext db=new MyDataContext();

IQueryable<ProductCategory> categories= db.Categories;

foreach (Category c in categories){
  Console.WriteLine (c.Name);
  foreach (ProductSubCategories sub in c.ProductSubCategories){
    Console.Writeline (sub.Name);
  }  
}

ok but if I want to pass my categories in the model to the partial view, what can i do ????

My strongly typed user control view look like this :

<%@ Control Language="C#" Inherits="System.Web.Mvc.ViewUserControl<IEnumerable<myData.Models.ProductCategory>>" %>

Answer 4

Yes, my primary and foriegn keys are correctly set.. The problem is when I try to use in my Partial view (strongly typed with <IEnumerable<myData.Models.ProductCategory>>) the second foreach statement, because there isn't any object in my Model. Whit Visual Studio's intellisense I can't find any type of object, only Methods! Please Help me

Thanks