Often, when we query for some set of entities, we also need to load a few children of those entities. In the Eager loading with… recipe, in the previous chapter, we learned how to use different fetch
syntaxes to eager load referenced entities and collection. We concluded that there are a few scenarios where the standard methods don't work as desired. In this recipe, we'll show you how to use subqueries and/or NHibernate's Futures, together with the session cache, to overcome these issues.
Complete the Getting Ready instructions at the beginning of Chapter 4, Queries.
AdvancedEagerLoading
to the QueryRecipes
project.Recipe
to the folder:using System.Linq; using NH4CookbookHelpers.Queries; using NH4CookbookHelpers.Queries.Model; using NHibernate; using NHibernate.Linq; namespace QueryRecipes.AdvancedEagerLoading { public class Recipe : QueryRecipe { } }
Recipe
to fill the database with test data:protected override void AddData(ISessionFactory sessionFactory) { using (var session = sessionFactory.OpenSession()) { using (var tx = session.BeginTransaction()) { for (var i = 1; i <= 20; i++) { var movie = new Movie { Name = "Movie" + i, UnitPrice = i }; movie.AddActor("Actor" + i, "Role" + i); movie.AddActor("Second Actor" + 1, "Second Role" + i); session.Save(movie); } tx.Commit(); } } }
protected override void Run(ISession session) { var baseQuery = session.Query<Movie>() .Where(x => x.Name.StartsWith("Movie")) .OrderBy(x => x.Name) .Skip(5) .Take(5); var movies = session.Query<Movie>() .Where(x => baseQuery.Contains(x)) .OrderBy(x => x.Name) .FetchMany(x => x.Actors) .ToList(); Show("A page of movies", movies); var allProducts = session.Query<Product>() .OrderBy(x => x.UnitPrice) .ToFuture(); session.Query<Movie>() .FetchMany(x => x.Actors) .ToFuture(); session.Query<Book>() .Fetch(x => x.Publisher) .ToFuture(); Show("All products",allProducts); }
AdvancedEagerLoading
recipe.In this recipe, we will try to solve the problems that we described at the end of the Eager loading with LINQ recipe. There were three problems:
We saw that eager loading a collection would render limiting functions, such as Skip
, Take
, SetFirstResult
, and SetMaxResults
useless. The limits would be applied to the entire query, which is not what we want. To solve that, we have to change the query into something slightly more advanced. Our base query, with the criteria and
limits all set, looks similar to the following example:
var baseQuery = session.Query<Movie>() .Where(x => x.Name.StartsWith("Movie")) .OrderBy(x => x.Name) .Skip(5) .Take(5);
We can use this as a subquery in the following query:
var movies = session.Query<Movie>() .Where(x => baseQuery.Contains(x)) .OrderBy(x => x.Name) .FetchMany(x => x.Actors) .ToList();
The result is a SQL query that looks similar to the following example SQL Server:
select movie0_.id as id0_0_, actors1_.id as id2_1_, movie0_.NAME as name0_0_, movie0_.description as descript4_0_0_, movie0_.unitprice as unitprice0_0_, movie0_.director as director0_0_, actors1_.actor as actor2_1_, actors1_.role as role2_1_, actors1_.movieid as movieid2_1_, actors1_.movieid as movieid0__, actors1_.id as id0__ from product movie0_ left outer join actorrole actors1_ on movie0_.id=actors1_.movieid where movie0_.producttype='Movie' and movie0_.id IN ( select movie2_.id from product movie2_ where movie2_.producttype='Movie' and movie2_.name like (@p0+'%') order by movie2_.name asc offset @p1 rows fetch first @p2 rows only ) order by movie0_.name asc
What's interesting here is that the Contains
call, Where(x => baseQuery.Contains(x))
, has been rendered as an IN
subquery. The subquery performs the actual limiting function, in this case, using SQL Server's OFFSET/FETCH
without being affected by any joins. Instead, the FetchMany
on the outer query gives us the eager loading that we want.
The performance of a query similar to this depends on how well the DBMS manages to optimize the execution, but generally it should work well.
In our recipe, we want to load all the Products
and present them in a list format. For movies, we show the names of the Actors
and for books we include the name of the Publisher
. However, since the query is on the base type, Product
, we have no way to specify the eager loading of those properties. This is where the Future
queries come in handy.
Our recipe uses three Futures queries. The first simply returns all products, sorted by unit price. The second Futures query, shown next, has the secret sauce:
session.Query<Movie>() .FetchMany(x=>x.Actors) .ToFuture();
You have probably observed that we don't actually assign the resulting IEnumerable<Movie>
to a variable and use it anywhere. That's because we don't actually care about the results of this query. Its only purpose is to sneak into the session's hidden MultiQuery
for Futures and get executed.
We do the same for the books:
session.Query<Book>() .Fetch(x => x.Publisher) .ToFuture();
When we enumerate the result of the first query, all three queries get executed. First, NHibernate loads up all the products, including movies and book, and then puts them in the session cache. At this point, every movie's Actors
collection is uninitialized. When NHibernate executes the second query, it initializes the collections as it loads the query results. Finally, when the last query is executed, all books get their Publisher
set and get initialized.
The end result is that we can output the name of a movie's Actors
or a book's Publisher
, without causing another query. That data has already been loaded.
The following are the resulting SQL queries:
select product0_.Id as Id0_, product0_.Name as Name0_, product0_.Description as Descript4_0_, product0_.UnitPrice as UnitPrice0_, product0_.ISBN as ISBN0_, product0_.Author as Author0_, product0_.PublisherId as Publishe8_0_, product0_.Director as Director0_, product0_.ProductType as ProductT2_0_ from Product product0_ order by product0_.UnitPrice asc select movie0_.Id as Id0_0_, actors1_.Id as Id2_1_, movie0_.Name as Name0_0_, movie0_.Description as Descript4_0_0_, movie0_.UnitPrice as UnitPrice0_0_, movie0_.Director as Director0_0_, actors1_.Actor as Actor2_1_, actors1_.Role as Role2_1_, actors1_.MovieId as MovieId2_1_, actors1_.MovieId as MovieId0__, actors1_.Id as Id0__ from Product movie0_ left outer join ActorRole actors1_ on movie0_.Id=actors1_.MovieId where movie0_.ProductType='Movie' select book0_.Id as Id0_0_, publisher1_.Id as Id1_1_, book0_.Name as Name0_0_, book0_.Description as Descript4_0_0_, book0_.UnitPrice as UnitPrice0_0_, book0_.ISBN as ISBN0_0_, book0_.Author as Author0_0_, book0_.PublisherId as Publishe8_0_0_, publisher1_.Name as Name1_1_ from Product book0_ left outer join Publisher publisher1_ on book0_.PublisherId=publisher1_.Id where book0_.ProductType='Book'
As we mentioned in the previous chapter, eager loading more than one collection in a query will give us a result, which in SQL terms is called a CROSS JOIN, a Cartesian product. Eager loading three different collections, with 100 items in each, will result in 100*100*100 (one million) rows and maybe that's just for one of the items in our output. Cleary, this should be avoided and again the Futures make it possible.
While we don't actually show it in the recipe, we could use the same strategy as we did for the Actors
property. Just add more Future queries, one per collection that we want to eager load:
session.Query<Movie>() .FetchMany(x=>x.Actors) .ToFuture(); session.Query<Movie>() .FetchMany(x=>x.MakeUpArtists) .ToFuture(); session.Query<Movie>() .FetchMany(x=>x.StageBuilders) .ToFuture();
In this recipe we only used LINQ, but the Future strategy can be used with all the querying techniques. Just combine what you learned in the corresponding Eager loading with… recipe with the correct Future syntax. The paging subquery might be a bit trickier and currently, it can't be solved in HQL but works fine with Criteria
and QueryOver
.
Refer the following code for Criteria
:
var baseCrit = DetachedCriteria.For<Movie>() .Add(Restrictions.Like("Name", "Movie", MatchMode.Start)) .AddOrder(new Order("Name", true)) .SetProjection(Property.ForName("Id")) .SetFirstResult(5) .SetMaxResults(5); var movies = session.CreateCriteria<Movie>() .Add(Subqueries.PropertyIn("Id", baseCrit)) .AddOrder(new Order("Name", true)) .SetFetchMode("Actors", FetchMode.Join) .List<Movie>();
Refer the following code for QueryOver
:
var baseQuery = QueryOver.Of<Movie>() .Where( Restrictions.On<Movie>(x => x.Name) .IsLike("Movie", MatchMode.Start) ) .OrderBy(x => x.Name).Asc .Select(x=>x.Id) .Skip(5) .Take(5); var movies = session.QueryOver<Movie>() .WithSubquery .WhereProperty(m => m.Id) .In(baseQuery) .Fetch(x => x.Actors).Eager .List();
3.138.35.255