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

Something like this ought to work

public partial class Product
{
    public IEnumerable<Feature> Features
    {
        get
        {
            return ProductFeatures.SelectMany(pf => pf.Feature);
        }
    }
}

Products.Where(p => SearchFeatures.All(sf => p.Features.Count(f => f.ID == sf.ID) > 0));

Answer 2

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 3

IQueryable<Product> query = db.Products
  .Where(p => SearchFeatures
    .All(sf =>
      p.ProductFeatures.Select(pf => pf.Feature).Contains(sf)
    )
  );

Answer 4

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