Nhibernate queryover filter based on count of child collection

Question

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.

xanatos · Accepted Answer · 2015-04-29T13:05:36.273

2

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/)

edited Apr 29 '15 at 13:05

answered Apr 29 '15 at 12:50

xanatos

109,618
12
197
280

@Seth You'll have to build the join through the many-to-many table in the subquery – xanatos Apr 29 '15 at 14:35
Ok, Soon as I get it working I'll mark this correct. It looks right. – Seth Apr 29 '15 at 14:40
Struggling with the join clause, running into getting the entire table rowcount. The rest of the search seems to work fine with the subquery. – Seth Apr 29 '15 at 15:27

Nhibernate queryover filter based on count of child collection

1 Answers1