Dynamic where clause using Linq to SQL in a join query in a MVC application

Question

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);
}

Answer 1

The tricky part about this is sending the whole list into the database as a filter. Your approach of building up more and more where clauses can work:

productsInCategory = ProductRepository
  .Where(p => p.Category.Name == category); 

foreach (PropertyFilter pf in filterList) 
{
     PropertyFilter localVariableCopy = pf;
     productsInCategory = from product in productsInCategory
       where product.ProductProperties
         .Any(pp => pp.PropertyValueId == localVariableCopy.ValueId)
       select product; 
}

---

Another way to go is to send the whole list in using the List.Contains method

List<int> valueIds = filterList.Select(pf => pf.ValueId).ToList();

productsInCategory = ProductRepository
  .Where(p => p.Category.Name == category)
  .Where(p => p.ProductProperties
    .Any(pp => valueIds.Contains(pp.PropertyValueId)
  );

Answer 2

IEnumerable<int> filters = filterList.Select(pf => pf.ValueId);

var products = from pp in ProductPropertyRepository
               where filters.Contains(pp.PropertyValueId) 
               && pp.Product.Category.Name == category
               select pp.Product;

Bear in mind that as Contains is used, the filters will be passed in as sproc parameters, this means that you have to be careful not to exceed the sproc parameter limit.