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", "b.Publisher")
                .UniqueResult<Book>();

            Show("Book:", book);

            var movies = session.CreateSQLQuery(@"
                  select {m.*}, {ar.*} from Product m
                  left join ActorRole ar ON ar.MovieId=m.Id
                  where m.ProductType = 'Movie' 
               ")
              .AddEntity("m", typeof(Movie))
              .AddJoin("ar", "m.Actors")
              .AddEntity("m", typeof(Movie))
              .SetResultTransformer(
                 Transformers.DistinctRootEntity)
              .List<Movie>();

            Show("Movies:", movies);
        }
    }
}
  3. Run the application and start the EagerLoadingWithSql recipe.
  4. Inspect the query log to make sure that the related entities included in the queries did not cause any extra, lazy loading.

How it works…

For a detailed explanation of the eager loading mechanism, see the recipe Eager loading with LINQ.

In native SQL there are no special keywords to use, such as fetch in HQL. But here there is one addition to the SQL, which doesn't look quite native. Instead of just writing select * to get all the columns, we specify that we want each mapped column from each specified table/alias, using the special syntax {alias.*}. NHibernate will parse the SQL query and replace these parts with fully qualified column references.

We also need to specify the alias that is the root entity, by calling AddEntity and then AddJoin to further define that the second alias represents a subproperty of the root entity.

The double call to AddEntity in the second query is necessary, since the result transformer will use the entry added last (through AddEntity or AddJoin) to deduce what it should perform its "distincting" operation on. Had we not added the extra AddEntity, we would end up with a list of ActorRoles.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.133.152.159