C# LINQ select items tricky filtering

Question

I'm using C# 3.5 I have an IList<MyItemLinkProperty>, where MyItemLinkProperty represents the link between an item and it's properties (one or many). It has ItemId, PropertyId, PropertyValue. In this list ItemId can occur many times as an item can have many properties, Color, size etc. (It's a performance related issue that I have this sort of list and not mapped to an item entity).

                  ItemID  PropId  PropValue
itemProperty1 = { 1001,   1,      'Red' }
itemProperty2 = { 1001,   2,      'Small' }
itemProperty3 = { 1002,   1,      'Red' }
itemProperty4 = { 1002,   3,      'Medium' }
itemProperty5 = { 1003,   4,      'Green' }
itemProperty6 = { 1003,   2,      'Small' }

Now I need to find all items that has property A and property B. For example 'red' and 'small'. This should give me ItemID 1001 that has both these properties.

In pseudo code I think I'm after "give me items where property id is 1 or 2 AND the item id is the same". Then I know a got items that has both these properties.

I'm thinking a linq query would do it. But have not gotten this to work and got stuck. Maybe I'm blocking my mind here, over thinking it and making it too complex...

Any tips for best solution for this?

Answer 1

You need to group by ItemID, then examine each group for containing all values, like this:

var smallRedIds = allItems
    .GroupBy(i => i.ItemID)
    .Where(g => g.Any(x => x.PropId == 1 && x.PropValue == "Red")
             && g.Any(x => x.PropId == 2 && x.PropValue == "Small"))
    .Select(g => g.Key);

This produces an enumeration of all item IDs that have both "small" and "red" property.