Contains at least everything in the entity structure

Question

I have the following 2 entities 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 identifiers that are passed to 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 submitted to 1, 2, 3 in action, only those articles that had these 3 tags or more will be returned.

How can i achieve this?

Thanks for the great answer!

All your answers gave the correct result, so I did a quick, basic profiling in sql, and these were the results based on your queries.

Answer 1

Use Except () and Any () , e.g.

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

Except() will provide you with items from the first list that do not exist in the second list

The Except statement creates the specified difference between two sequences. It will only return elements in the first sequence that are not displayed per second.

Answer 2

This should do it:

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

Answer 3

Try the following:

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

Answer 4

Iteratively plotting 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? } 

Answer 5

How about this?

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

Answer 6

Use this:

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