Selecting objects not in a collection in NHibernate with ICriteria Interface

Question

In my system Users own 0 or more Categories. Here is a simplified version of my model classes:

public class User
{
    public virtual String Name {get; set;}
    public virtual IList<Category> Categories { get; set; }
}

public class Category
{
    public virtual String Title {get; set;}
}

I now want to create an ICriteria query to select all categories that aren't assigned to a user, but I'm stuck. Ideally I don't want to create a navigation property from Category to User, but with my beginner knowledge of NHibernate that's the only solution I can see.

Is there an ICriteria query that will do this with the current data model classes?

Thanks for your help.

Answer 1

This is off the top of my head, but might be a useful pointer.

var crit = _session.CreateCriteria<Category>("c")
                      .Add(
                        Subqueries.PropertyNotIn("c.id",
                            DetachedCriteria.For<User>("u")
                                .CreateCriteria("Categories","uc")
                                .SetProjection(Projections.Property("uc.id"))                                    
                        ));
var unassignedCategories = crit.List<Category>();

You can probably get a feel for the SQL that will be generated here:

select c.* from categories where c.id not in (select uc.id from usercategories)

Hope this helps, and sorry I haven't been able to test it :)

Tobin