Tuna Toksoz's weblog

This is a recent question raised in NHibernate Users Group. The user wanted to realize the following query with Criteria api.

var result = db.Person.Where(x => x.Pets.Count > 0 && x.Alive)
                      .OrderBy(x => x.Name);

This is not a simple query, but it has a solution

DetachedCriteria crit = DetachedCriteria.For(typeof (Person), "p2")
    .CreateCriteria("p2.Pets","Pets")
    .Add(Restrictions.EqProperty("p.Id", "p2.Id"))
    .SetProjection(Projections.Count("Pets.Id"));

ICriteria c = s.CreateCriteria(typeof (Person), "p")
    .Add(Restrictions.Gt(Projections.SubQuery(crit), 0))
    .Add(Restrictions.Eq("p.Alive",true))
    .AddOrder(Order.Asc("p.Name"));

What we had to do is to create a DetachedCriteria and on that execute CreateCriteria so that we can do querying on our collection.

The other way is simpler, but requires you to use HQL (below query is provided by Fabio Maulo)

session.CreateQuery("from Person p where  size(p.Pets) > 0 and p.Visible = true order by p.Name")

or

session.CreateQuery("from Person p where  p.Pets.size > 0 and p.Visible = true order by p.Name")

I hope this helps.

nhibernate