Linq with where clause in many-to-many EF Code First object

Question

In an MVC4 project, using code first, I have the following:

public class Course 
{
   public string CourseId { get; set; }
   public string Name { get; set; }
   public bool IsActive { get; set; }

   public virtual Categories ICollection<Category> { get; set; }

   public Course()
   {
      Categories = new HashSet<Category>();
   }
}

public class Category
{
   public string CategoryId { get; set; }
   public string Name { get; set; }

   public virtual Courses ICollection<Course> { get; set; }

   public Category()
   {
      Courses = new HashSet<Course>();
   }
}

Code First then rightly creates an extra table for this relation, since it is many-to-many.

My problem is when I need a list of Categories with the active Courses (IsActive==true). This here, for example, is not possible:

var categories = db.Categories.Where(x => x.Courses.Where(y => y.IsActive)).ToList();

Any input how I get a list of the categories with only the active courses?

Thanks!

Answer 1

This query will return all categories that have at least one course that is active:

var categories = db.Categories
    .Where(x => x.Courses.Any(y => y.IsActive))
    .ToList();

This query will return all categories whose courses are all active. If, for example, a category has two courses and one of the courses is inactive, that category will be excluded from the query:

var categories = db.Categories
    .Where(x => x.Courses.All(y => y.IsActive))
    .ToList();