EF Intersect Syntax

Question

A UI allows users to select one or many tags. I would like to select all Nodes that have ALL that tags the user entered associated, not just a single tag.

public JsonResult SearchNodesByTags(string[] tags)
{
    var dbTags = _DbContext.Tags.Where(t => tags.Contains(t.DisplayName)).ToList();
    var nodes = _DbContext.Nodes.Where(n => n.Tags.Intersect(dbTags).Any());  
    // Error about intersection with non primitive

    return Json(nodes);
}

Gert Arnold · Accepted Answer · 2015-06-19T21:49:08.067

9

You can do this in one statement:

var nodes = _DbContext.Nodes
            .Where(n => n.Tags.All(t => tags.Contains(t.DisplayName)));

Your statement is not correct because dbTags is a local list containing Tag objects. When you use this list in a LINQ-to-Entities expression there is no way to translate these objects into SQL variables. That's only possible with primitive values.

edited Jun 19 '15 at 21:49

answered Jun 19 '15 at 21:42

Gert Arnold

105,341
31
202
291

1

That worked, you just forgot the: **t =>** tags.Contains(t.DisplayName) If you modify this, I will mark your answer as correct. – blgrnboy Jun 19 '15 at 21:48
Actually, I am getting a circular reference problem now. This is probably because I have a many to many relationship between Nodes and Tags. – blgrnboy Jun 19 '15 at 21:59
I'd propose you put this into a new question, because I need more details to see what exactly is going on. I don't know where/how you get this circular reference problem. – Gert Arnold Jun 19 '15 at 22:01
I solved that problem, however, it seems that I am getting the incorrect result. Here is my query: var nodes = _DbContext.Nodes.Where(n => n.Tags.All(t => tags.Contains(t.DisplayName))).Select(n => new {n.NodeNativeId, n.NodeName, n.NodeClass.ClassName}).ToList(); I am getting back a node that isn't associated with the tag. It also happens to be the name with the highest ID. – blgrnboy Jun 19 '15 at 22:07
Again, please ask a new question if subsequent issues arise. It's very hard to read code in comments and it's impossible to give enough details. For instance, "incorrect results" needs lots more explanation. – Gert Arnold Jun 19 '15 at 22:10
http://stackoverflow.com/questions/30948048/ef-linq-return-entities-that-contain-an-entire-collection – blgrnboy Jun 19 '15 at 22:17
@jjj See my comments [here](http://stackoverflow.com/questions/30948048/ef-linq-return-entities-that-contain-an-entire-collection#comment49936049_30948285) – Gert Arnold Jun 20 '15 at 06:57
@GertArnold: ah I think I see what you were trying to do. Something like `.Where( n => tags.All(t => n.Tags.Select(t2 => t2.DisplayName).Contains(t)))`? Not sure if that would work with SQL generation though...and I'm on the wrong computer to try that out. I was more pointing out that your answer wouldn't quite give what the OP wanted. – jjj Jun 20 '15 at 19:49
@jjj That would create a query with as many `UNION`s as tags. Better than `n` `UINION`s with single row queries that `EXCEPT` produces, but still not too good. Anyway, it all depends on the meaning of "all" how this should be implemented in the end. There are three meanings of "all" equivalent to left outer join, full join or right outer join. I'm thinking of writing an answer that brings it all together and also elaborates on effects on query shape. (To OP's second question, but begging not to accept it. It's no competition, I just like the collegial discourse we have about the subject). – Gert Arnold Jun 20 '15 at 20:02
Come to think of it, this question will be a good place for it as well. – Gert Arnold Jun 20 '15 at 20:04

score 2 · Answer 2 · edited May 23 '17 at 12:00

var nodes = _DbContext.Nodes.Where(n => n.Tags.Intersect(dbTags).Any());

First of all, you can't use objects in a Linq-to-Entities expression, so you'd have to use something like this to compare:

n.Tags.Select(t => t.DisplayName).Intersect(tags)

Second, Intersect will give you the set of items that are in both given sets, so you'll end up with all Nodes that has any of the tags, instead of all nodes that have all of the tags.

If you want all the Nodes that contain every Tag in tags, you might want to use the answer from here on subsets:

_DbContext.Nodes
    .Where(n => !tags.Except(n.Tags.Select(t => t.DisplayName)).Any()) 
    .Select(...

set1.Except(set2) contains elements of set1 that aren't in set2
!set1.Except(set2).Any() == true if set2 includes every element of set1

EF Intersect Syntax

2 Answers2

Linked