3

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

Michael
  • 1,351
  • 1
  • 11
  • 25
  • so you are using many to many relationship . What have you tried so far ? where u got stuck ? – yasmuru Dec 31 '13 at 06:11
  • No, it's a one-to-many: One Venue to many Events. And frankly, I'm not exactly sure where to begin. I have to query Venues and somehow query each Venue's associated Events to find the earliest Event that's later than Today and then return the Venue only if that Event exists and has a Type of "Wedding". There seem to be many different directions one might go with the NH library and I'm not sure how to do this in any of them. – Michael Dec 31 '13 at 07:01

1 Answers1

2

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

Community
  • 1
  • 1
Radim Köhler
  • 122,561
  • 47
  • 239
  • 335
  • It could give you pretty clear idea how to. It is not so complicated, but complex. At the end, you can get really powerful queries, applied on DB engine... returning small portion of data ;) – Radim Köhler Dec 31 '13 at 07:14
  • It looks like it should work for me, and I'll be happy to vote you up once I verify that. As it happens, your example is actually very close to my real one, as opposed to the one I used here for illustration. I did have a couple of questions, though, which I posted as comments on the link you gave me. Thanks. – Michael Dec 31 '13 at 07:28
  • I appended my ideas/answers to your points. In general I would say, that SelectList is a way how to improve reading, saying: I will select more values. Select could (I guess) be used as well. In the deepest query we need to select 2 values, because both of them are used later. If you are talking about optimisation like Take TOP.. Order By... I would say: try it. Because maybe it could help, but I would say that this could be left on the DB engine. It will be reponsible for creation of the best execution plan. But... why not try to improve it ;) – Radim Köhler Dec 31 '13 at 08:05