Search code examples
sqlnhibernatequeryovernhibernate-projections

Nhibernate queryover filter based on count of child collection

Ultimately I want to filter all parent objects that have less than 2 children.

I'm building a search screen with a grid dashboard, which uses the following logic as a example of how to build a query. 

var query = Session.QueryOver<Parent>(() => parentAlias);

if (!string.IsNullOrWhiteSpace(SearchCriteria.OpenedBy))
    query.Where(Restrictions.Eq(Projections.Property<Parent>(x => x.OpenedBy), SearchCriteria.OpenedBy));

if (SearchCriteria.OpenedDateStart != null)
    query.Where(Restrictions.Ge(Projections.Property<Parent>(x => x.OpenedAt), SearchCriteria.OpenedDateStart));

This is working wonderfully right up until this point:

if (!string.IsNullOrEmpty(SearchCriteria.ChildrenAffected) && SearchCriteria.ChildrenAffected == "Multi")
    query.Where(() => parentAlias.Children.Count > 2);

It makes sense that .Count does't work, this isn't really linq. Also .Count() throws an error. Honestly, I feel like I've tried every combination of Restritions, JoinAlias, etc that I can think of, but I've gone off the path of educated attempts and into the realm wild guessing a long time ago.

How do I set up a query to filter out a parent based on the count of children in the QueryOver syntax?

-----NOTE ----- I Debated using linq after id gotten my list, but I'm doing paging in the query set up so the filter would be applied after the page came back.

Solution

  • You need a subquery...

    Children childrenAlias = null;
var subquery = QueryOver.Of<Children>(() => childrenAlias)
     .Where(() => childrenAlias.Parent.ID == parentAlias.ID)
     .ToRowCountQuery();
query.WithSubquery.WhereValue(2).Le(subquery);

    Note that I don't know how to do Count > 2, so I'm doing 2 <= Count, and there is the possibility that instead of 

    .Where(() => childrenAlias.Parent.ID == parentAlias.ID)

    you can write

    .Where(() => childrenAlias.Parent == parentAlias)

    Mmmh... if you reall need Count > 2 you should be able to:

    query.Where(Restrictions.Gt(Projections.SubQuery(subquery), 2));

    or 

    query.WithSubquery.Where(() => subquery.As<int>() > 4);

    (this one I haven't ever used... taken from http://blog.andrewawhitaker.com/blog/2014/10/24/queryover-series-part-8-working-with-subqueries/)