In our model with Movies and ActorRoles, we may eventually have movies with hundreds of actors. If we want to display a movie and show all the roles played by Harrison Ford, we certainly can:
foreach (var actorRole in movie.Actors
.Where(x=>x.Actor=="Harrison Ford"))
{
Console.WriteLine("Harrison Ford played {0} in {1}",
actorRole.Role,
movie.Name);
}While that would work fine in small data sets, there's a risk that we will load a lot more data than necessary, just to show a couple of roles. What happens is that as soon as we're using the Actors property on each Movie instance, we trigger the lazy loading mechanism and a query will be executed to fetch all ActorRoles of that movie. The Where expression specified doesn't affect this behavior, since it's effectively executed in memory.
For scenarios like this, NHibernate offers a function where an uninitialized (not yet loaded from database) collection can be filtered and ordered, so that only the relevant rows are retrieved.
- Add a folder named
CollectionFiltersto the project. - Add a class named
Recipeto the folder having the following code:using System; using NH4CookbookHelpers.Queries; using NH4CookbookHelpers.Queries.Model; using NHibernate; namespace QueryRecipes.CollectionFilters { public class Recipe : QueryRecipe { protected override void Run(ISession session) { var movie = session.Get<Movie>(1); var actorFilter=session .CreateFilter(movie.Actors, "WHERE Actor=:actor"); actorFilter.SetString("actor", "Harrison Ford"); var actors = actorFilter.List<ActorRole>(); foreach (var actorRole in actors) { Console.WriteLine( "Harrison Ford played {0} in {1}", actorRole.Role, movie.Name); } } } } - Run the application and start the
CollectionFiltersrecipe.
When you run the recipe you will see in the query log that one query is executed to fetch the movie, and after that a query which only fetches ActorRoles where Actor='Harrison Ford'. That's exactly what we wanted!
The filter instance created by the CreateFilter method is actually an IQuery, just as the ones created by CreateQuery. This IQuery is automatically configured with a FROM clause to only fetch objects of the collection's element type (in this case ActorRole) and a WHERE restriction to get the items belonging to this particular relation. The extra criteria specified in the query string are added to the query.
A standard query performing the same task could look similar to this:
var actorQuery = session.CreateQuery( @"SELECT m.Actors FROM Movie m INNER JOIN m.Actors ar WHERE m=:movie AND ar.Actor=:actor"); actorQuery.SetEntity("movie", movie); actorQuery.SetString("actor", "Harrison Ford");
Using CreateFilter is usually more convenient, since all we need to specify is which collection instance we want to filter.
Filtering collections this way only makes sense when the entities we want to filter have not been loaded yet. This includes scenarios where the filter is applied to properties or sub collections, which themselves would trigger one or several lazy loads.
In the recipe, had we already loaded the Actors collection, either by lazy loading or using one of the eager loading mechanisms, it would have been better to just stick with the in memory Where implementation. NHibernate provides ways to check whether an entity has been initialized or not and we can use that to decide which querying technique to use at runtime:
if (NHibernateUtil.IsInitialized(movie.Actors))
{
actors = movie.Actors.Where(x => x.Actor == "Harrison Ford");
}
else
{
actors = session
.CreateFilter(movie.Actors,"WHERE Actor=:actor")
.SetString("actor", "Harrison Ford")
.List<ActorRole>();
}Now, that may look a bit convoluted, and it is. Thankfully, several extensions have been developed, which makes this process transparent. One of those comes from Ricardo Peres, and the necessary code can be copied from his blog post at http://weblogs.asp.net/ricardoperes/querying-an-uninitialized-collection-with-nhibernate.
Using that code, we can not only rest assured that the best querying technique is used, but also specify the filter as a convenient lambda expression:
var actors = movie.Actors.Query()
.Where(x => x.Actor == "Harrison Ford");