EF Many to Many select intersection

Question

I'm trying to implement a tagging system with C# entity framework. I cannot get the query required for the case that two or more tags are expected to all be present to return a result. I have a many to many relationship (just FKs, DB first) and I am attempting to get an object when all selected tags exist. Object - LookupTable - Attributes. I parse the selected tags into a list and then try to get only those objects for which all tags in this list are present. It appears to result in what I'd expect from an "Any" operator, not the "All".

List<string> intersectTags = new List<string>();

foreach (object i in ef.objects.Where(o => o.Attributes.All(attribute =>
intersectTags.Contains(attribute.AttributeNK))))

Update: Also needed to get instances where ef.Object had more tags than intersectTags. Filtering for instances where intersectTags is a subset of Object.Attributes.

Khanh TO · Accepted Answer · 2015-01-17T08:56:11.017

1

Your code fails in case your Attributes is a subset of selected tags.

If you are looking to match when intersectTags is a subset of o.Attributes, try reversing the check.

Unfortunately, Linq to Entity does not support this kind of syntax, we need ToList() to load the objects and perform Linq To Objects.

It should work but there is a performance implications (I'll post an update if I have a better solution):

List<string> intersectTags = new List<string>();

foreach (object i in ef.objects.ToList().Where(intersectTags.All(tags =>
o.Attributes.Any(attribute => attribute.AttributeNK == tags))))

edited Jan 17 '15 at 08:56

answered Jan 11 '15 at 08:49

Khanh TO

48,509
13
99
115

Excellent, thanks! Makes perfect sense when I see it... new enough to EF that I was doubting myself in other areas instead of looking at possible set issues in the comparison. Also changed to >= to not miss anything, but avoid the subset issue you identified. – Volvox Jan 11 '15 at 17:12
Well that solves one issue and introduces another... how can I match when o.Attributes has more tags than intersectTags? In that case I am looking to match when intersectTags is a subset of o.Attributes. – Volvox Jan 11 '15 at 17:25
@ Volvox: See my updated answer with `Any` and reversing the `All` check. In this case, we may not need >= – Khanh TO Jan 12 '15 at 02:40
tags turns into string type which then doesn't work. I changed list type to Attribute and then do a subset test based on this answer: http://stackoverflow.com/questions/407729/determine-if-a-sequence-contains-all-elements-of-another-sequence-using-linq which is: foreach (object i in ef.objects) {(!subset.Except(superset).Any()) ...} If you want to update I will choose answer as I might not have got here without your help. – Volvox Jan 13 '15 at 00:16
@Volvox: It did not work because of my mistake, `o` should come before `Attributes` instead of `intersectTags`. When I changed the code, I forgot about that. I have updated the answer. – Khanh TO Jan 13 '15 at 02:25
@Volvox: It would be appreciate if you tell me whether my updated answer works (to fix my mistake). I'm very interested to know. – Khanh TO Jan 14 '15 at 13:48
This does not work. Lamba types do not allow the functionality you listed here. – Volvox Jan 16 '15 at 17:15
@Volvox: with the solution you pointed out, we have performance implications that we have to load data to memory to do filtering on it. It's so sad that Linq to Entity does not support this syntax. I have updated my answer with `ToList()` to turn it into Linq To Objects (which has the same performance implications as yours). In case you don't want to convert the list, you could try my updated answer. – Khanh TO Jan 17 '15 at 08:59

Paolo Costa · Answer 2 · 2015-01-11T07:05:09.483

I don't know if I understood well, if so I can give a solution in plain SQL. You have to lookup for all the records that contain one of the requested tag and then group them by the productId with the clause HAVING COUNT equals the number of tags you are passing.

SELECT ProductId FROM ProductTag
WHERE  TagId IN (2,3,4)
GROUP BY ProductId
HAVING COUNT(*) = 3

Here's a demo:

http://sqlfiddle.com/#!3/dd4023/3

I'm sorry, currently I cannot give you an implementation in EF (don't have Visual Studio with me), I did something similar for LINQ TO SQL and it uses the PredicateBuilder class, you can find it here:

http://www.codeproject.com/Articles/36178/How-to-manage-product-options-with-different-price

Paolo

EF Many to Many select intersection

2 Answers2