EF LINQ - Return entities that contain an entire collection

Question

I am trying to troubleshoot the following LINQ Query:

public JsonResult SearchNodesByTags(string[] tags)
{    

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

    return Json(nodes);
}

The query is returning a single node that is not associated with a tag. What I want it to do, is return any nodes that have ALL the tags.

You have to add `n.Tags.Any() && n.Tags.All(...`. That eliminates nodes without tags. I don't see how the query can't return any nodes if there really are existing tags in the `tags` variable. — Gert Arnold, Jun 19 '15 at 22:30

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

  .Where(n => n.Tags.All(t => tags.Contains(t.DisplayName)))

The way this is currently constructed, you're only going to end up with the Nodes where every tag in Node.Tags has a name in the tags whitelist, which includes Nodes with no 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

Edit

It was pointed out in the comments that using Except could generate problematic queries, so I was thinking another option was to get a superset from the database, and further filter the objects within the application:

_DbContext.Nodes
    // filter nodes with any of the input tags
    .Where(n => n.Tags.Any(t => tags.Contains(t.DisplayName)))

    // select the information required
    .Select(n => new {
        n.NodeNativeId, 
        n.NodeName, 
        ClassName = n.NodeClass.ClassName,
        TagNames = n.Tags.Select(t => t.DisplayName) })

    // materialize super set with database
    .ToList()

    // filter so that only nodes with all tags remain
    .Where(n => !tags.Except(n.TagNames).Any())

    // produce result in anonymous class
    .Select(n => new { n.NodeNativeId, n.NodeName, n.ClassName })
    .ToList();

Edit 2

I just saw another one here that might work for you, but it requires that Tag.DisplayName is unique, since it could fail if you have multiple tags with the same DisplayName:

_dbContext.Nodes
     .Where(n => n.Tags.Count(t => tags.Contains(t.DisplayName)) == tags.Count)
     .Select(...

Did you check the query that's generated using `Except`? `Except` with local lists generates terrible queries that quickly exceed maximum nesting level. I've learned to stay away from it. — Gert Arnold, Jun 20 '15 at 06:55
@GertArnold: ah I didn't know that. In that case maybe it would be better to pull down a superset and do further filtering using linq-to-objects. — jjj, Jun 20 '15 at 19:52
I would like to implement the Edited answer, however, it seems that there are some syntax errors there. The select on line 3 is not valid. — blgrnboy, Jun 22 '15 at 18:57
@blgrnboy: Ah yeah, it should be `Select(t => t.DisplayName)` to get names, and not `Select(t.DisplayName)` — jjj, Jun 22 '15 at 19:09
Sorry, what I meant was, after n.Tags.Any(t => tags.Contains(t.DisplayName)) - .Select is not valid here. — blgrnboy, Jun 22 '15 at 19:13
@blgrnboy: oooooh, I'm missing a closing parenthesis after the `Any`...the `Select` is supposed to build off the `Where` — jjj, Jun 22 '15 at 19:41

EF LINQ - Return entities that contain an entire collection

1 Answers1

Linked