NHibernate - LINQ Query Many to Many Issue

Question

I'm trying to upgrade an existing application to use NHibernate. My database has the following tables:

Sites:
- Id (PK)
- Name

Categories:
- Id (PK)
- Name

CategoriesSite
- CategoryId (PK)
- SiteId (PK)
- Active

For each category and site a record may or may not exist in the CategoriesSite table. If an item exists in the CategoriesSite table then it can turn the Category off by setting Active to false. If it doesn't then it assumes Active is true.

I'd like to create a LINQ query in NHibernate to filter for categories of a particular site (that are active). For example say I have the following data:

Sites:
Id | Name
1  | Site 1
2  | Site 2

Categories:
Id | Name
1  | Category 1
2  | Category 2

CategoriesSite:
CategoryId | SiteId | Active
1          | 1      | True
1          | 2      | True
2          | 1      | False

I could say:

var categories = session.Query<CategorySite>()
                        .Where(s => s.Site.Id == 2 && s.Active)
                        .Select(s => s.Category)
                        .ToList();

However this will only get Category 1 and not Category 2 which I'd like it to do. I was wondering if anyone has done anything similar and could suggest either a way to query this or offer any recommendations on how I can map this scenario better.

Answer 1

I think i've solved this. I added a one to many collection against the Category for the list of Categorory Sites. This allows me to say:

var categories = session.Query<Category>()
                        .Where(c => !c.Sites.Any(s => s.Site.Id == 2 && !s.Active))
                        .ToList();

This means it will only not return the category when it has been set in-active and will still return the Category when no record exists in the CategoriesSite table.