get entites with relation count greater than x with nhibernate

Question

Say i have a entity like this

public class Something
{
    public int Id{get;set;}
    public string Name{get;set;}
    public IList<SomeOther> Assosiation{get;set}
}

how can i query with nhibernate to get all the Something entities with more than 10 Assosiations using Criteria API?

Cheers Colin

Answer 1

Something like this:

var dc = DetachedCriteria.For<SomeOther>()
    .SetProjection(Projections.GroupProperty("Something.Id"))
    .Add(Restrictions.Gt(Projections.RowCount(), 10));

var criteria = session.CreateCriteria(typeof (Something))
    .Add(Subqueries.PropertyIn("Id", dc));

Which would produce something like this:

SELECT this_.Id             as Id7_0_,
       this_.Title          as Title7_0_
FROM   Something this_
WHERE  this_.Id in (SELECT   this_0_.SomethingId as y0_
                    FROM     SomeOther this_0_
                    GROUP BY this_0_.SomethingId
                    HAVING   count(* ) > 10 /* @p0 */)