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.