Grouping problem
-
I have two tables "Categories" and "SubCategories". Category Table: ID: 1, 2, 3, 4, 5 ..... CatName: Cat1, Cat2, Cat3, Cat4, Cat5 .... SubCategories Table: ID: 1, 2, 3, 4, 5 ..... SubCatName: SubCat1, SubCat2, SubCat3, SubCat4, SubCat5 .... MainCatID: 1, 1, 3, 4, 1, 2 .... //the ID of the related Category for the subcategory Now I want to select all the Categories and also list the appropriate subcategorie within that category. Here is the query I wrote, but it shows an error on the "WHERE" clause: Dim menu = From m In db.ProductCategories _ Select New With {.Title = m.CategoryName, _ .SubCategories = From s In m.ProductSubCategories _ Where s.MainCategoryID = m.ProductSubCategories _ Select New With {.SubTitle = s.SubCategoryName}} For Each item In menu.ToArray() MsgBox(item.Title) For Each subT In item.SubCategories.ToArray() MsgBox(subT.SubTitle) Next Next I also tried this: Dim menu = From m In db.ProductCategories _ Select New With {.Title = m.CategoryName, _ .SubCategories = From s In m.ProductSubCategories _ Group s By m.ProductSubCategories Into Group _ Select New With {.SubTitle = Group}} I'm not sure what I'm doing wrong. Still a beginner with Linq and trying to learn. Any help and explanation is appreciated. Thanks
-
I have two tables "Categories" and "SubCategories". Category Table: ID: 1, 2, 3, 4, 5 ..... CatName: Cat1, Cat2, Cat3, Cat4, Cat5 .... SubCategories Table: ID: 1, 2, 3, 4, 5 ..... SubCatName: SubCat1, SubCat2, SubCat3, SubCat4, SubCat5 .... MainCatID: 1, 1, 3, 4, 1, 2 .... //the ID of the related Category for the subcategory Now I want to select all the Categories and also list the appropriate subcategorie within that category. Here is the query I wrote, but it shows an error on the "WHERE" clause: Dim menu = From m In db.ProductCategories _ Select New With {.Title = m.CategoryName, _ .SubCategories = From s In m.ProductSubCategories _ Where s.MainCategoryID = m.ProductSubCategories _ Select New With {.SubTitle = s.SubCategoryName}} For Each item In menu.ToArray() MsgBox(item.Title) For Each subT In item.SubCategories.ToArray() MsgBox(subT.SubTitle) Next Next I also tried this: Dim menu = From m In db.ProductCategories _ Select New With {.Title = m.CategoryName, _ .SubCategories = From s In m.ProductSubCategories _ Group s By m.ProductSubCategories Into Group _ Select New With {.SubTitle = Group}} I'm not sure what I'm doing wrong. Still a beginner with Linq and trying to learn. Any help and explanation is appreciated. Thanks
OK, I found my problem and got it to work. In case someone else has the same problem, here is the solution that worked for me: Dim menu = From m In db.ProductCategories _ Select New With {.Title = m.CategoryName, _ .SubCategories = From s In m.ProductSubCategories _ Where s.MainCategoryID = m.ID _ Select s.SubCategoryName, s.ID} Thanks again