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.
EagerLoadingWithSql
in the project.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); } } }
EagerLoadingWithSql
recipe.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
.
3.137.183.210