Contains At Least All in Entity Framework

Question

I have the following 2 entitys in my db.

public class Article
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    // Some code removed for brevity

    public virtual ICollection<Tag> Tags { get; set; }
}


public class Tag
{

    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    // Some code removed for brevity

    public virtual ICollection<Article> Articles { get; set; }

}

I need to filter these articles based on the tag IDs that are passed into my action.

public ActionResult FindAll(List<int> tags)
{

    //
    // I need to return all articles which have ALL the tags passed into this method
    //

    var query = ApplicationDbContext.Articles...


}

For example, if I passed in 1, 2, 3 into the action, only articles which had these 3 tags or more would be returned.

How can I achieve this?

Thanks for the great response!

All your answers produced the correct result so I did some quick, basic profiling in sql and this was the results based on your queries.

Query Results

For those `Except` and `All` answers, you might want to check to make sure the performance is acceptable for the load you're expecting, and that the generated SQL still works if `tags.Count` is large. See the comments under [this](http://stackoverflow.com/questions/30947278/ef-intersect-syntax/30948394#comment49949396_30947688) for more details. It *might* be worth querying a superset of articles (with any of the tags?) and further filtering. — jjj, Jun 22 '15 at 16:36
if none of the answers worked or you are still facing trouble, let me know so I can help — AmmarCSE, Jun 22 '15 at 23:22
I'm curious: how many tags did you use for these tests, how large was the `Articles` table, and how large was the result set? — jjj, Jun 23 '15 at 18:58

AmmarCSE · Accepted Answer · 2015-06-22T18:36:52.603

3

Use Except() and Any() like

ApplicationDbContext
    .Articles
    .Where(a => tags.Except( a.Tags.Select( t => t.Id ).ToList() ).Any() == false)

Except() will give you the items from the first list which do not exist in the second list

The Except operator produces the set difference between two sequences. It will only return elements in the first sequence that don't appear in the second.

edited Jun 22 '15 at 18:36

answered Jun 22 '15 at 16:07

AmmarCSE

30,079
5
45
53

This only works for articles that have only the matching tags and no more.... right? – Brad C Jun 22 '15 at 16:12
I don't think this does what the OP wants as he stated he wants *at least* the tags specified, not *only*. – adam0101 Jun 22 '15 at 16:13
@adam0101, ive corrected my answer and it should work based off http://stackoverflow.com/questions/5620266/the-opposite-of-intersect – AmmarCSE Jun 22 '15 at 16:16
Looks like yours works now... nice solution. I tried to come up with a 1 liner and failed. +1 – Brad C Jun 22 '15 at 16:23
The article tag is an object with an Id property, not an integer. I think you need to tweak this a little more. – adam0101 Jun 22 '15 at 16:42
I'd be curious to see how the execution plan differs between this and my solution. If this were Linq-to-entities, I would expect mine to run faster because this one looks at every tag in the article to get the Id where as mine would exit the loop as soon as it found all the necessary tags, but SQL Server is a different animal. – adam0101 Jun 22 '15 at 19:02
@AmmarCSE Thank you for your answer. For some reason I expected SQL to generate two queries since you're enumerating the tags by calling ToList but it didn't. – heymega Jun 23 '15 at 08:22

adam0101 · Answer 2 · 2015-06-22T18:15:31.477

2

This should do it:

ApplicationDbContext.Articles.Where(a => tags.All(t => a.Tags.Any(at => at.Id == t)));

edited Jun 22 '15 at 18:15

answered Jun 22 '15 at 16:26

adam0101

29,096
21
96
174

I never even knew an `All` existed. Nice! – AmmarCSE Jun 22 '15 at 19:10

ocuenca · Answer 3 · 2015-06-22T19:56:07.093

2

Try this:

  var query =from a in ApplicationDbContext.Articles
             where a.Tags.Count(t => tags.Contains(t.Id)) == tags.Count
             select a;

edited Jun 22 '15 at 19:56

answered Jun 22 '15 at 16:51

ocuenca

38,548
11
89
102

I think your first revision was correct. This is now matching the exact number of tags, which isn't what the OP wanted. – adam0101 Jun 22 '15 at 18:13
@adam0101, the first query returned the articles with at least one tag that matches in the tags list. Now, with the second query I'm not just checking the number of tags are the same, check the condition in the subquery, if the amount of contained tags matches with amount of elements in the `tags` list, then I select the article – ocuenca Jun 22 '15 at 19:09
2

`where a.Tags.Count(t => tags.Contains(t.Id)) == tags.Count` ? – jjj Jun 22 '15 at 19:50

Brad C · Answer 4 · 2015-06-22T16:16:59.877

1

Iteratively building the result through IQueryable.

public ActionResult FindAll(List<int> tags)
{
    var queryable = ApplicationDbContext.Articles.AsQueryable();

    foreach(var t in tags)
    {
        queryable = queryable.Where(w => w.Tags.Any(a => a.Id == t));
    }

    queryable.AsEnumerable();   // stuff this into a viewmodel and return actionresult?
}

edited Jun 22 '15 at 16:16

answered Jun 22 '15 at 16:10

Brad C

2,868
22
33

Thanks for your answer! I saw in another comment you tried to do this as a one liner. This is possible using the Aggregate method. tags.Aggregate(ApplicationDbContext.CaseStudies.AsQueryable(), (current, t) => current.Where(w => w.Tags.Any(a => a.Id == t))); – heymega Jun 23 '15 at 08:20

score 1 · Answer 5 · answered Jun 22 '15 at 17:02

1

How about this?

var articles = ApplicationDbContext.Articles.Where (a => a.Tags.Select (t => t.Id).Intersect(tags).Count()>=tags.Count);

answered Jun 22 '15 at 17:02

SKG

152
5

ranquild · Answer 6 · 2015-06-22T16:29:36.103

0

Use this:

ApplicationDbContext.Articles.Where(a => tags.All(t => a.Tags.Contains(t)));

edited Jun 22 '15 at 16:29

answered Jun 22 '15 at 16:15

ranquild

1,799
1
16
25

This isn't correct. He wants to match all the specified tags, not all the tags on the article. – adam0101 Jun 22 '15 at 16:19
That looks right now except that the article tag is an object with an Id property, not an integer. – adam0101 Jun 22 '15 at 16:37

score 0 · Answer 7 · answered Jun 19 '22 at 11:04

For anyone trying this and receiving the error "... could not be translated", it seems that .All() doesn't work on in-memory collections such as those from API query parameters. One solution is first convert tags to IQueryable e.g.:

var tags = ApplicationDbContext.Tags.Where(t => tags.Contains(t.Id));
var query = ApplicationDbContext.Articles
    .Where(a => tags.All(t => a.Tags.Contains(t)));

Another solution is to use .Count and .Contains instead of .All:

var query = ApplicationDbContext.Articles
    .Where(a => a.Tags.Count(t => tags.Contains(t.Id)) == tags.Count());

Contains At Least All in Entity Framework

7 Answers7

Linked