project lookup table to object with is selected over many to many

Question

I need help coming up with the right linq statement to project my data.  Say we have a many-to-many:

table product (pk[productid], label);
table product_with_category (pk[productid,categoryid]);
table category (pk[categoryid], label);

Now for the business object I want to project into I have the following classes:

class ProductUI {
  int productid,
  string productname,
  List<CategoryUI> CatList
}

class CategoryUI {
  int categoryid,
  string categoryname,
  bool IsSelected
}

Say I have productid 47.  I want to project into a ProductUI class with CatList with IsSelected set to true or false based on whether or not there is a match in the matrix table for product 47 to all the categories in the category table.

Thanks,

-Ethan Nelson

Answer 1

Ok, after a few hours and one very false positive, the following:

from a in products
let cats = (from z in categories
	orderby z.categoryname
	select new
	{
		z.categoryid,
		z.categoryname,
		isselected = (z.product_in_categories.Any(x => x.categoryid == z.categoryid && x.productid == a.productid))
	})
select new
{
	a.productid,
	a.productname,
	cats
}

This does the job.

-Ethan Nelson

Answer 2

Hi Ethan Nelson,

Welcome to MSDN Forum.

I'm glad to hear that you have solved the issue, and thanks for your sharing.

Best Regards

---

Allen Li [MSFT]
MSDN Community Support | Feedback to us