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?

What have you tried? People won't help you if you don't show any effort in trying to solve the problem... — Rui Jarimba, Jan 31 '13 at 11:27
"People won't help you if you don't show any effort in trying to solve the problem.." Its not always true ) — MikroDel, Jan 31 '13 at 11:28
I felt I was up the wrong alley anyways and wanted "untainted" ideas so I tried asking a question for tips if anyone had some. Glad someone had! — Martin, Jan 31 '13 at 13:01

score 6 · Accepted Answer · answered Jan 31 '13 at 11:28

6

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.

answered Jan 31 '13 at 11:28

Sergey Kalinichenko

714,442
84
1,110
1,523

1

Work like a charm, thank you! Now I learnt something new today with grouping and conditions. With this working, I was able to improve performance by so much it's silly. – Martin Jan 31 '13 at 12:59

score 2 · Answer 2 · answered Jan 31 '13 at 13:23

I have come across similar issue and solved it using JOIN. It could be then easily used to generate dynamic queries:

int[] propertyIds = new []{1,2,3,4};
var query = dc.ItemProperties.Where(i=> i.PropId == propertyIds[0]);

for (int i = 1; i < catsGroups.Length;i++)
{
    query = query.Join(dc.ItemProperties.Where(i=> i.PropId == propertyIds[i]), x => x.IDItem, x => x.IDItem,(x, y) => x);
}
return input;

There is an advantage that this will let you project all wanted columns (unlike with GROUP BY), which could be helpful in some cases. Also the performance of generated SQL is very good (there are no subqueries like when using Any or All)

score 1 · Answer 3 · answered Jan 31 '13 at 11:29

1

You could define a list of perperties that you need, e.g.:

var desiredProperties = new[]{ "Red", "Small" };

Then you can use Enumerable.All and Contains:

var allMatchingItemProperties = allItemProperties
    .Where(ip => desiredProperties.All(dp => ip.Properties.Contains(dp)));

answered Jan 31 '13 at 11:29

Tim Schmelter

450,073
74
686
939

Looks very clean which I like. However I needed to match multiple criteria as above, not sure this solution works in that case. But thanks anyways, it was a clean solution that I'll keep in mind for future filtering problems. – Martin Jan 31 '13 at 13:10

C# LINQ select items tricky filtering

3 Answers3