LINQ to SQL many to many int ID array criteria query

Question

Ok this should be really simple, but I am doing my head in here and have read all the articles on this and tried a variety of things, but no luck.

I have 3 tables in a classic many-to-many setup.

ITEMS ItemID Description

ITEMFEATURES ItemID FeatureID

FEATURES FeatureID Description

Now I have a search interface where you can select any number of Features (checkboxes). I get them all nicely as an int[] called SearchFeatures.

I simply want to find the Items which have the Features that are contained in the SearchFeatures.

E.g. something like:

return db.Items.Where(x => SearchFeatures.Contains(x.ItemFeatures.AllFeatures().FeatureID))

Inside my Items partial class I have added a custom method Features() which simply returns all Features for that Item, but I still can't seem to integrate that in any usable way into the main LINQ query.

Grr, it's gotta be simple, such a 1 second task in SQL. Many thanks.

Answer 1

The following query will return the list of items based on the list of searchFeatures:

from itemFeature in db.ItemFeatures
where searchFeatures.Contains(itemFeature.FeatureID)
select itemFeature.Item;

The trick here is to start with the ItemFeatures table.

---

It is possible to search items that have ALL features, as you asked in the comments. The trick here is to dynamically build up the query. See here:

var itemFeatures = db.ItemFeatures;

foreach (var temp in searchFeatures)
{
    // You will need this extra variable. This is C# magic ;-).
    var searchFeature = temp;

    // Wrap the collection with a filter
    itemFeatures =
        from itemFeature in itemFeatures
        where itemFeature.FeatureID == searchFeature
        select itemFeature;
}

var items =
    from itemFeature in itemFeatures
    select itemFeature.Item;