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

Question

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, Name, UrlName]

Properties [Id, CategoryId, Name, UrlName]

PropertyValues [Id, PropertyId, Text, UrlText]

ProductPropertyValues [ProductId, PropertyValueId]

When I add a product to the catalog, multiple ProductPropertyValues 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. The business logic and SQL indexes and constraints make sure that all UrlNames and texts are unique for values properties and categories.

The solution will be a MVC3 EF code first based application and the routing is setup as followed:

/products/{categoryUrlName}/{*filters}

The filter routing part has a variable length so multiple filters can be applied. Each filter contains the UrlName of the property and the UrlText of the value separated by an underscore.

An url could look like this /products/websites/framework_mvc3/language_csharp

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.

Maybe there is another way to implement the filters. All larger web shops use category depending filters and I am still looking for the best way to implement the persistence part for this type of functionality. The suggested solutions result in an "or" resultset if multiple filters are selected. I can imagine that adding a text property to the product table in which all property values are stores as a joined string can work as well. I have no idea what this would cost performance wise. At leased there will be no complex join and the properties and their values will be received as text anyway.

Maybe the filtering mechanism can be done client side ass well.

possible duplicate of [LINQ - dynamic WHERE clause?](http://stackoverflow.com/questions/848415/linq-dynamic-where-clause) — Craig Stuntz, May 28 '10 at 15:29
There is something that may be making the solution difficult to express. With the schema design above, it is possible to have a product with a property value that is not part of the products category. — Peter Willis, May 31 '10 at 10:51

score 0 · Answer 1 · answered Feb 28 '14 at 22:55

I came up with a solution that even I can understand... by using the 'Contains' method you can chain as many WHERE's as you like. If the WHERE is an empty string, it's ignored (or evaluated as a select all). Here is my example of joining 2 tables in LINQ, applying multiple where clauses and populating a model class to be returned to the view.

public ActionResult Index()
{
    string AssetGroupCode = "";
    string StatusCode = "";
    string SearchString = "";

    var mdl = from a in _db.Assets
              join t in _db.Tags on a.ASSETID equals t.ASSETID
              where a.ASSETGROUPCODE.Contains(AssetGroupCode)
              && a.STATUSCODE.Contains(StatusCode)
              && (
              a.PO.Contains(SearchString)
              || a.MODEL.Contains(SearchString)
              || a.USERNAME.Contains(SearchString)
              || a.LOCATION.Contains(SearchString)
              || t.TAGNUMBER.Contains(SearchString)
              || t.SERIALNUMBER.Contains(SearchString)
              )
              select new AssetListView
              {
                  AssetId = a.ASSETID,
                  TagId = t.TAGID,
                  PO = a.PO,
                  Model = a.MODEL,
                  UserName = a.USERNAME,
                  Location = a.LOCATION,
                  Tag = t.TAGNUMBER,
                  SerialNum = t.SERIALNUMBER
              };


    return View(mdl);
}

score 0 · Answer 2 · answered May 28 '10 at 15:59

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

i would definitely check the datacontext log on this one to see how the query is actually being generated. you may have to tweak to make sure it uses an "IN" operator rather than pulling the whole query into memory and doing the "contains" filter in C#. — luke, May 29 '10 at 00:20
Always a good idea to monitor the generated query form. I've used .Contains so many times for this purpose it's now second nature for me. LinqToSql makes a parameter with each item in the list, then uses all those parameters in IN. — Amy B, May 29 '10 at 00:59

score 0 · Answer 3 · answered May 31 '10 at 11:13

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.

score 0 · Answer 4 · answered Feb 28 '18 at 17:58

I know this an old answer but if someone see's this I've built this project:

https://github.com/PoweredSoft/DynamicLinq

Which should be downloadable on nuget as well:

https://www.nuget.org/packages/PoweredSoft.DynamicLinq

You could use this to loop through your filter coming from query string and do something in the lines of

query = query.Query(q =>
{
    q.Compare("AuthorId", ConditionOperators.Equal, 1);
    q.And(sq =>
    {
        sq.Compare("Content", ConditionOperators.Equal, "World");
        sq.Or("Title", ConditionOperators.Contains, 3);
    });
});

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

4 Answers4