CODEWITHSUNDEEP
c#asp.netlinqlinq-to-sqlcontains
vts
vts

Reputation: 1717

LINQ results matching ALL relationships

I have a many to many relationship as follows:

Products Table: 

ProductID 
Description

ProductFeatures Table: 

FeatureID 
ProductID 
FeatureValue

Any Product can have many Features. I have a Feature class

public class Feature
{
    public Guid FeatureID { get; set; }
    public string FeatureValue { get; set; }
}

And a list of Features that come from a search

List<Feature> searchFeaturesList = new List<Feature>();

foreach (string var in Request.QueryString)
{
    searchFeaturesList.Add(new Feature { FeatureID = new Guid(var.ToString()),   FeatureValue = Request.QueryString[var] });
}

How can I get a list of all products that match ALL the feature IDs AND feature Values on the list using LINQ to SQL. I tried using Contains but I get a result of all the products that match ANY of the features. I need it to match ALL the features. Please note that each FeatureID can have different FeatureValue Thank you

Upvotes: 1

Views: 136

Answers (1)

Adrian Iftode
Adrian Iftode

Reputation: 15663

var query = Products.AsQueryable();

foreach (var feature in searchFeaturesList)
{
     // create here new values of featureId and featureValue otherwise will get the first ones from the iterator
     var featureId = feature.FeatureID;
     var featureValue = feature.FeatureValue;
     // each feature id and value is tested if exists in the ProductFeatures
     query = query.Where(p=>p.ProductFeatures
                             .Any(pf=> pf.FeatureID == featureId && pf.FeatureValue == featureValue));
}

Upvotes: 2

Related Questions