CODEWITHSUNDEEP
c#linq
Chris Hobbs
Chris Hobbs

Reputation: 817

Find objects in list where all properties exists in another list

I have a database structure similar to this

itemID   |   attributeID
------------------------
   1     |       1
   2     |       1
   1     |       2

I need to find a way to find all items that match on a list of attributes. I have it working where I can select items that match on ANY of the attributes, using this LINQ query

List<int> attributes = new List<int>(){ 1, 2};

List<int> listOfItemIDs = (from item in db.ItemsToAttributes
                           where attributes.Contains(item.attributeID)
                           select item.itemID)
                           .Distinct()
                           .ToList();

And this code will return back a list of int's, specifically {1,2}, because itemID 1 matched on attribute 1 (and 2), and itemID 2 matched on attribute 1.

I can't figure out how to grab the list of itemID's where it matches on ALL items in the attribute list. So in the example given above, I would need it to return JUST itemID 1, because itemID one matched both on attribute 1 AND attribute 2.

I've been looking at using an Intersect or an Except, but I can't seem to get either to work.

Upvotes: 0

Views: 1024

Answers (1)

GWhite
GWhite

Reputation: 426

var result = db.ItemsToAttributes
               .GroupBy(x => x.itemID)
               .Where(x => !x.Select(y => y.attributeID).Except(attributes).Any() 
            && !attributes.Except(x.Select(y => y.attributeID)).Any()).Select(x => x.Key);

You're going to want to group them by the Item IDs so you know which attribute IDs correspond to each of your eligible Item IDs. This gives you an IEnumerable of IGrouping where the group keys are the Item IDs. Then you can use the grouped attribute ID collections and check them against the attributes collection. If all the items exist in both collections, the item ID in question has all the right attributes. The final .Select returns the Keys (Item IDs) of the items whose attributes match the provided list.

Upvotes: 2

Related Questions