Eager loading with SQL

As a final step in our walk-through of eager loading techniques, we've come to the native SQL queries. SQL should always be a last resort, but if it's necessary it certainly supports eager loading.

Getting ready

Complete the Getting Ready instructions at the beginning of this chapter.

How to do it…

1. Create a new folder named EagerLoadingWithSql in the project.

2. Add a new class named Recipe to the folder:

   using NH4CookbookHelpers.Queries;
   using NH4CookbookHelpers.Queries.Model;
   using NHibernate;
   using NHibernate.Transform;
   
   namespace QueryRecipes.EagerLoadingWithSql
   {
       public class Recipe : QueryRecipe
       {
           protected override void Run(ISession session)
           {
               var book = session.CreateSQLQuery(@"
                     select {b.*}, {p.*} from Product b
                     left join Publisher p 
                    ON b.PublisherId=p.Id
                     where b.ProductType = 'Book'")
                   .AddEntity("b", typeof(Book))
                   .AddJoin("p...