Using join on a subquery with NHibernate

NHibernate is one of the best ORM solution available. That said sometimes its not easy to find a way around your problem. I have been trying to create a join on a subquery and want to uses "EXISTS" on a table to be more efficient.

Here is my class design

All I want is to get list of flytoys for a purchase order
- filter on customer name
- get the oldest purchase order

After trying many various methods, here is the code snippet that worked for me:

  public FlyToy[] GetPendingToys(int Count)
        {
            using (var session = OpenSession())
            {
                PurchaseOrder po = null;
                FlyToy toy = null;
                QueryOver subQuery = 
                    QueryOver.Of(() => po)
                        .SelectList(list => list
                            .SelectGroup(() => po.Toy.ID)
                            .SelectMin(() => po.OrderDate))
                        .Where(Restrictions.EqProperty(
                            Projections.Property(() => po.Toy.ID),
                            Projections.Property(() => toy.ID)))
                        .Where(p => p.CustomerName == "Saif");


                var mainQuery = QueryOver.Of(() => toy)
                    .Fetch(t => t.Discount).Eager
                    .WithSubquery.WhereExists(subQuery).Take(Count);

                IList results = mainQuery.GetExecutableQueryOver(session)
                    .List();

                FlyToy[] FlyToys = results.ToArray();
                return FlyToys;
            }
        }

Be nice to nerds. Chances are you'll end up working for one.
- Charles Sykes