Eager loading child collections

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.

Getting ready

Complete the Getting Ready instructions at the beginning of Chapter 4, Queries.

How to do it...

  1. Add a new folder named AdvancedEagerLoading to the QueryRecipes project.
  2. Add a new class named 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
  {
  }
}
  3. Add a new method to 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();
    }
  }
}
  4. Add a new method to test the queries:
    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);
}
  5. Run the application and start the AdvancedEagerLoading recipe.
  6. Check the query log.

How it works...

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:

Problem 1 – limiting the result set doesn't work

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.

Problem 2 – only the queried class' properties can be eager loaded

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'

Problem 3 – eager loading multiple collections should be avoided

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();

There's more…

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.

Criteria

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>();

QueryOver

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();

See also

  • Eager loading with LINQ
  • Eager loading with Criteria
  • Eager loading with QueryOver
  • Eager loading with HQL
  • Using Futures
..................Content has been hidden....................

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