Lazy loading is about "when associated entities are loaded". There is another angle to loading associated entities which is about "how associated entities are loaded". The latter is controlled by a feature called fetching strategy. A simple example here would help. Suppose you want to retrieve all benefit records for a particular employee. There are different ways of doing this. For instance, following are two different SQL queries that I can think of to retrieve benefit records:
Employee
and Benefit
tableBenefit
table using a WHERE
clause that limits the records by matching foreign key column Employee_Id
to the ID of the employee recordNHibernate supports three different ways of fetching associations. Through fetching strategy you can tell NHibernate which way it should use for a particular association. From optimization point of view, this is a good lever to use. Fetching strategy is declared as part of mapping. It works globally once set in mapping. Eager fetching that we looked at in the previous chapter gives similar control over how associated entities are fetched from database. Eager fetching is local to a query, unlike fetching strategies. Also, eager fetching only gives you two options – lazy (select) and eager (join). Fetching strategy gives us a third option – subselect.
Let's take a look at different strategies and what difference they bring about to loading of associated entities.
Select fetching uses an independent SELECT
statement to load associated entities. This is default fetching strategy where none is declared explicitly. If you want, you can still declare it explicitly. Following is how you declare it during mapping of an association:
Set(e => e.Communities, mapper => { //.. other mappings..// mapper.Fetch(CollectionFetchMode.Select); }, relation => relation.ManyToMany(mtm => { //.. other mappings..// }));
Preceding code is from mapping of the Communities
collection on the Employee
entity. The CollectionFetchMode
enumeration defines different fetching strategies that we are going to discuss next.
Select fetching underpins lazy loading. We have discussed lazy loading at length in the previous chapter. Section Avoid select N+1 problem of this chapter discusses select fetching in more detail. In order to avoid repetition, I intend not to spend more time here explaining how select fetching works. Let's look at other fetching strategies.
You may not want to issue multiple SELECT
statements to load associations of an entity. You may rather prefer a SQL join that returns the root entity and associated entities in a single SELECT
statement. If that is the case then you have got join fetching at your disposal. Enabling join fetching is not much different from enabling select fetching. You use CollectionFetchMode.Join
for this purpose. Following is a shortened version of code showing how to enable join fetching:
//.. other mapping code mapper.Fetch(CollectionFetchMode.Join); //.. other mapping code
Since join fetching would use SQL join between root entity table and associated entity table, both the root and associated entities are loaded at the same time using single SELECT
statement. This effectively stops lazy loading of associations. This is true even if you have explicitly enabled lazy loading. If you intend to use lazy loading then do not enable join fetching. Eager fetching that we discussed in the previous chapter and join fetching are exactly the same. The only difference between them is that one is local to the query and the other is global and declared in the mapping. So everything that applies to eager fetching also applies to join fetching.
Eager fetching does not come without its own problems – specifically, the problem of duplicate records being loaded. In section Avoid eager fetching we will discuss this problem and solution to the problem. Another point worth mentioning is the behavior when lazy loading is enabled and join fetching is specified at the same time. Do you remember from lazy loading discussion of the previous chapter, that NHibernate uses special collection wrapper types and proxies when lazily loading collections and single-ended associations? If you are using join fetching even after enabling lazy loading, NHibernate would continue to return collection wrapper types and proxies even though associations are not loaded lazily. This should not make much of a difference to your code but it is worth keeping in mind.
Subselect fetching offers a middle ground between join fetching and select fetching. Select fetching results in multiple SELECT
statements being sent to database. Join fetching does the job in single SELECT
but results in duplication of root entity.
Subselect uses two SELECT
statements:
This is difficult to understand without a proper example. Let's use the same code we used in the Batching lazy collections section. This code loads employees living in London and then accesses the Benefits
collection on each employee instance in a foreach
loop.
var employees = Database.Session.Query<Employee>() .Where(e => e.ResidentialAddress.City == "London"); foreach (var employee in employees) { foreach (var benefit in employee.Benefits) { //do something with benefit here } }
If the Benefits
collection on Employee
is configured to use subselect fetching then we would get the following two SELECT
statements when the preceding code is run:
SELECT * -- columns from Employee table FROM employee employee0_ INNER JOIN address address1_ ON employee0_.id = address1_.employee_id WHERE address1_.city = 'London' /* @p0 */ SELECT * --columns from Benefit table FROM benefit benefits0_ WHERE benefits0_.employee_id IN (SELECT employee0_.id FROM employee employee0_ INNER JOIN address address1_ ON employee0_.id = address1_.employee_id WHERE address1_.city = 'London' /* @p0 */)
The first SELECT
would load all the employees residing in London. The second SELECT
statement, which is triggered when the Benefits
collection on the first employee instance is accessed, loads the associated benefits for all the employees that were loaded in the previous SELECT
statement. Note that the second SELECT
uses the modified version of the first SELECT
in its WHERE
clause – which is also called subselect.
Following code enables subselect fetching on the Benefits
collection of Employee
:
Set(e => e.Benefits, mapper => { mapper.Key(k => k.Column("Employee_Id")); mapper.Cascade(Cascade.All.Include(Cascade.DeleteOrphans)); mapper.Inverse(true); mapper.Fetch(CollectionFetchMode.Subselect); }, relation => relation.OneToMany(mapping => mapping.Class(typeof(Benefit))));
Subselect fetching does not disable lazy loading but it does pre-fetch all associations when the first association is accessed. This gives a batching-like effect. But be careful with this option. Once this is enabled globally, there is no way to turn this off in individual queries. For small collection sizes, this may work well but you cannot predict when the collection will grow in size in production.
Reality is that there is no one right fetching strategy. More than one factor are at play in deciding what results in optimal data access operation. Fetching strategy just happens to be one of the factors. So do not try to stick to one strategy that may have worked for you in a particular situation. Try different strategies in combination with lazy loading, batching, and so on. Also try to figure out how your collections might be accessed at different points in code. Use all of that knowledge to choose the right strategy for a particular data access requirement.
Next two sections are more of guidelines around two problems that NHibernate beginners tend to overlook. We will try to understand the problem and look at different ways of fixing the problems.