Geek Answers Handbook

C # LINQ selects complex filtering elements

I am using C # 3.5. I have IList<MyItemLinkProperty> , where MyItemLinkProperty represents the link between an element and its properties (one or more). It has ItemId , PropertyId , PropertyValue . In this list, ItemId can occur many times, since an item can have many properties, color, size, etc. (This is a performance issue that I have such a list and is not tied to an element object).

  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 the elements that have property A and property B. For example, "red" and "small". This should give me ItemId 1001, which has both of these properties.

In the pseudocode, I think that after "give me the elements where the property id is 1 or 2 and the element identifier is the same." Then I know the items that have both of these properties.

I think the linq query will do this. But it didn’t work out so that it worked and got stuck. Maybe I'm blocking my mind here, thinking about it and making it too complicated ...

Any tips for a better solution?

+4
source share
3 answers

You need to group by ItemID , and then examine each group containing all the values, for example:

 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 leads to an enumeration of all identifiers of elements that have both "small" and "red" properties.

+5
source

You can define a list of parameters you need, for example:

 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)));
+1
source

I ran into a similar problem and decided to use it JOIN. Then it can be 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;

The advantage is that it allows you to project all the columns you need (unlike GROUP BY), which may be useful in some cases. In addition, the generated SQL performance is very good (no subqueries, for example, when using Any or All )

+1
source

More articles:


All Articles