Blogger Widgets
  • Sharing Photos using SignalR
  • TFS Extenstion - allows copy work items between projects
  • Displaying jquery progressbar with ajax call on a modal dialog
  • Managing windows services of a server via a website
  • Exploring technologies available to date. TechCipher is one place that any professional would like to visit, either to get an overview or to have better understanding.

Search This Blog

Thursday 8 March 2012

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

2 comments:

Copyright © 2013 Template Doctor . Designed by Malith Madushanka - Cool Blogger Tutorials | Code by CBT | Images by by HQ Wallpapers