How to write this LINQ/EF query?

Question

So I have an Entity Framework 5 model that includes a many-to-many relationship.

CategoryValues --< CourseCategoryValues >-- Courses

I have a LINQ query that selects every Course in the database. I would really like to modify it to only select Courses that belong to a specific CategoryValue. My attempt thus far has failed?

Can anyone help me figure this out?

This is what I have tried:

Using database As SiteDataContext = New SiteDataContext
    database.Configuration.ProxyCreationEnabled = False
    database.Courses.Include("Classes")
    database.Courses.Include("CourseCategoryValues")
    query = (From c In database.Courses Select c Order By c.Name).Where(
                                                                        Function(c) 0 < c.Classes.Where(Function([class]) [class].Status.ToLower = "open").Count
                                                            ).Include(Function(r) r.Classes).Include(Function(r) r.CourseCategoryValues)
    ' Here is where I am trying to narrow down the query results
    If (pid.HasValue) AndAlso (0 <> pid.Value) Then
        query.Where(Function(c) c.CourseCategoryValues.Any(Function(v) v.CategoryValue.CategoryValueID = pid))
    End If

    model.PageData = query.ToList
End Using

Answer 1

I think you are only missing the assignment of the filter to the query variable. Where returns a new queryable, it doesn't modify the queryable you apply the Where to. So, you would need:

query = query.Where(...)

The Where expression itself looks correct to me.