Dynamic where clause using Linq to SQL in a join query in a MVC application
- by jhoefnagels
Dear .Net Linq experts,
I am looking for a way to query for products in a catalog using filters on properties which have been assigned to the product based on the category to which the product belongs. So I have the following entities involved:
Products
-Id
-CategoryId
Categories
[Id]
Properties
[Id, CategoryId]
PropertyValues
[Id, PropertyId]
ProductProperties
[ProductId, PropertyValueId]
When I ad a product to the catalog, multiple ProductProperties will be added based on the category and I would like to be able to filter all products from a category by selecting values for one or more properties.
I will gather all filters, which I will hold in a list, by reading the URL. Now it is time to actually get the products based on multiple properties and I have been trying to find the right strategy but untill now it does not really work.
Is there a way to make this work without writing SQL?
I was trying something like this:
productsInCategory = ProductRepository.Where(p => p.Category.Name == category);
foreach (PropertyFilter pf in filterList)
{
productsInCategory = (from product in productsInCategory
join pp in ProductPropertyRepository on product.Id equals pp.ProductId
where pp.PropertyValueId == pf.ValueId
select product);
}
