Hi everyone,

I have a class "Product" which contains a collection of "Order". I'm looking for suggestions on how I can load every "Product", each with only the latest 10 "Order".

In SQL, querying the data would look something like this :
SELECT * FROM Product AS A LEFT OUTER JOIN (SELECT TOP 10 FROM Order ORDER BY CreateTime DESC) AS B ON A.ProductId = B.ProductId

I've been scratching my head for a day, and I can't get any way to do it properly (ICriteria, IQuery, ISQLQuery) using nHibernate. I'd greatly appreciate any suggestions people can offer.

Thanks a bunch,
--Gene

To solve this, I use a custom sql-query and loader.

<class name="Auction" lazy="false">
<id name="Id">
<generator class="guid" />
</id>

<bag name="Bids" lazy="false">
<key column="AuctionId"/>
<one-to-many class="VirtualBid.Domain.Bid,VirtualBid"/>
<loader query-ref="GetTopBids"/>
</bag>
</class>

<sql-query name="GetTopBids">
<load-collection alias="bid" role="VirtualBid.Domain.Auction.Bids"/>
<return alias="bid" class="VirtualBid.Domain.Bid, VirtualBid"/>
SELECT TOP 10 {bid.*} FROM Bid {bid} WHERE bid.AuctionId=? ORDER BY CreateTime DESC
</sql-query>