CODEWITHSUNDEEP
nhibernate
Michael
Michael

Reputation: 1421

NHibernate query with restriction on child collection

I've looked at plenty of examples on this site, but I'm still not sure how to do this:

For illustration, let's say I have persistent Venues, each of which has a collection of Events, where each Event has ReservationDate. If I want to get all the Venues whose next Event is of type "Wedding", how would I go about it? It requires selecting based on a value of a specific element (in this case the first ReservationDate > Today) in the child collection, that element being determined by a different restriction (Type == "Wedding").

I've looked at various queries using CreateCriteria, QueryOver, DetachedCriteria, JoinOver and the whole gamut of NH query options (I don't want to use HQL), but I'm still at a loss.

Your help is appreciated.

Michael

Upvotes: 3

Views: 1351

Answers (1)

Radim Köhler
Radim Köhler

Reputation: 123861

I've created very detailed example how to handle these situations. Please see all the details here:

The point is to create few Subqueries represented as DetachedCriteria. Using aliasing we can communicate among them (passing the ID).

At the end, we can SELECT clean/flat structure of the ROOT entity... while having full power of filtering based on referenced collecitons.

This approach has the biggest advantage in the fact, that we can apply the paging (Take(), Skip()) because the final select is on top of the root table

Upvotes: 2

Related Questions