c# - Constructing a linq query to get associations -

i have 2 tables, category , productcategory.

a product can have multiple categories:

category

categoryidcategoryname
1               electronics     
2               e-reader     
3               tablet     

productcategory

categoryidproductid
1               100     
2               100     
3               100     
3               500     
1               800     

i have repository set category has navigation property productcategory. trying construct linq query takes categoryid , return me list of associated categories + number of times associated (association being through product).

for example if take categoryid 1 (electronics) above example: can see 1 linked productid 100 , 800 @ same time productid 100 linked categories 2 & 3. see categoryid 2 linked 1 times productid 100 , categoryid 3 linked 2 times productids 100 , 500.

my expected result set (excluding categoryid of 1 passed in)

categoryidassociation(s)
2               1     
3               2     

but problem if this:

var resultset = repository.query() .include(pc => pc.productcategory) .where(c => c.categoryid == 1).tolist();

the resultset restricted

categoryidproductid
1               100     
1               800     

but using above linq want shape resultset to:

categoryidassociation(s)
2               1     
3               2     

any ideas ?

you're looking count of associations aggregate function can use directly against grouped selection product category.

var results = productcategory     .groupby(g => g.categoryid)     .select(new {          category = g.key,          associations = g.count()          })     .tolist();