LINQ is a lot for many hells - a request where CONTAINS ALL

Question

I have many, many relationships as follows:

Products Product Code Description

ProductFeatures ProductFeatureID FeatureId Product Code

FeatureId Features Description

Any product can have many functions.

Then I get down to iQueryable called "SearchFeatures", which contains two separate Feature objects that I want to execute.

I want to find Products that have ALL of these features!

eg. something like this would be nice:

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

What is the cleanest way to achieve this with 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 

That should do it. featIdList - list of function identifiers you are looking for

Answer 3

Something like this should 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 4

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

Answer 5

Not sure if it did not exist 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)));