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);
}

Answer 1

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.

Answer 2

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