LINQ many to many hell - querying where CONTAINS ALL

Question

I have a many to many relationship as follows:

Products ProductID Description

ProductFeatures ProductFeatureID ProductID FeatureID

Features FeatureID Description

Any Product can have many Features.

I then come along with an iQueryable called "SearchFeatures" which contains two particular Feature objects that I want to search on.

I want to find the Products which have ALL of these Features!

E.g. something like this would be nice:

return db.Products.Where(x => x.Features.ContainsAll(SearchFeatures));

What is the cleanest way to achieve this using LINQ?

Many thanks.

Answer 1

IQueryable<Products> products = db.Products;
foreach (var feature in SearchFeatures)
{
    Feature tmpFeature = feature;
    products = products
        .Where(x=>x.ProductFeatures.Any(y=>y.FeatureID == tmpFeature.FeatureID));
}

Answer 2

from item in db.Products
where item.ProductFeatures.Where(x=>featIdList.Contains(x.FeatureId)).Count() == featIdList.Count
select item

This should do it. featIdList is a list of the feature ids that you're looking for