Finding Products Present in Multiple Categories using LINQ to Ucommerce

Once in a while a question turns up in the Ucommerce support forum which has me stumped for while. As usual with LINQ once the solution presents itself it turns out to be quite simple, dare I say elegant?

So what was the question again? We need a way to find all products present in number of categories as specified by a customer, think search. So we only want products present in both categories A and B, but not products only present in only one or the other.

Here’s how you’d do that:

    
    
    // These are the categories our products must be present in
    string[] categoryNames = { "Software", "Support" };
     
    var query = from product in Product.All()
             where product.CategoryProductRelations.Where(x => categoryNames.Contains(x.Category.Name)).Count() == categoryNames.Count()
             select product;
    
    

The idea here is to first find all products for the categories we’re looking for. With that done we compare the number of categories found for each product with the number of categories we’re looking for (in our categoryNames array). If they match we’ve got he result we were after in the first place.

The cool thing about this query is that everything is translated to SQL behind the scene so the end result is very efficient execution of the query.