Nhibernate QueryOver Get all Parents with less than 3 childs

Question

I am fighting with this query since yesterday morning and cannot find a solution. What I want to do is to have a query like:

IList<Parent> = _session.QueryOver<Parent>()
   .Where(Restrictions.lt(x => x.Childs.Count,4))
   .List<Parent>();

Anyone having an idea how to do it? Childs is a HasMany-Collection.

Regards, Martin

score 2 · Answer 1 · answered Jul 05 '13 at 12:03

Here's one way you could do this, using a subquery:

Parent parentAlias = null;

IList<Parent> = _session.QueryOver<Parent>(() => parentAlias)
    .WithSubquery.WhereValue(4).Gt(
        QueryOver.Of<Child>()
            .Where(ch => ch.Parent.Id == parentAlias.Id)
            .Select(Projections.Count<Child>(ch => ch.Id)
        )
    .List<Parent>();

Which will generate something like this:

SELECT this_.Id
       /* select list continues */
FROM   [Parent] this_
WHERE  4 /* @p0 */ > (SELECT count(this_0_.Id) as y0_
              FROM   [Child] this_0_
              WHERE  this_0_.ParentId = this_.Id)

Nhibernate QueryOver Get all Parents with less than 3 childs

1 Answers1