LINQ many to many hell - querying where CONTAINS ALL

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 Featu开发者_如何转开发res!

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.

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

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

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

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

Not sure if it did not existed in 2010, but now you can do something like:

var myArray = new[] { 2, 3 };
q = myArray.Aggregate(q, (current, myArrayItem) => current.Where(x => x.Producs.Any(y => y.Id == myArrayItem)));