CODEWITHSUNDEEP
c#linqsql-server-2008c#-4.0linq-to-sql
David
David

Reputation: 432

Get categories and list of items in this categories

I trying to get list of categories and their items in the same query in Linq. something like list of category item and List of related products.

for example list of items like:

Class MyClass {
Category food;
List<Product> products; //here I want to get list of products in this category
}

And I have table that connect between them that contains the product id and the category id.

Which query should I use for getting this result?

Edit: example of the tables:

Table:Products

Table:ProductsCategories

Table: Categories

Upvotes: 2

Views: 2599

Answers (2)

Gert Arnold
Gert Arnold

Reputation: 109118

There is a many-to-many relationship between Product and Category. A very convenient way to query such relationships without an explicitly coded join is:

from c in Categories
select new MyClass { 
    Category = c, 
    Products = c.ProductCategories.Select (pc => pc.Product).ToList()
}

Upvotes: 1

dthorpe
dthorpe

Reputation: 36082

Try something like this: (untested)

var q = from pc in ProductsCategories
        from cat in Categories
        where pc.productId == cat.categoryId
        from prod in Products
        where prod.productId == pc.productId
        select new { category = cat, product = prod };
var q2 = from product in q
         group product by product.categoryId into g
         select new { categoryId = g.Key, products = g };

Upvotes: 0

Related Questions