Stop NHibernate N+1 selects with LINQ Fetch and FetchMany

The problem: one query becomes many

When you load a list of parent entities and then touch a related collection or reference, NHibernate may issue one additional SELECT for every parent row. This is the N+1 select problem: one query to fetch parents, plus N queries to fetch their associations. It appears when lazy loading is enabled and your code iterates results outside the session or touches navigation properties in a loop.

Useful takeaway

NHibernate LINQ offers Fetch and FetchMany to load selected associations in the same query. Fetch applies to single-ended associations such as many-to-one or one-to-one. FetchMany applies to collections. Both are query-scoped: they change the SQL for that LINQ query only and leave the mapping and other queries untouched.

Worked configuration

Assume entities Order, Customer, and OrderLine. Without eager fetching, this code can produce one SELECT for orders and one SELECT per order for its lines:

// Runs in application code, typically inside a repository or service method.
// Requires an open ISession and normal SELECT permissions on the database.
var orders = session.Query()
    .Where(o => o.Date >= startDate)
    .ToList();

foreach (var order in orders)
{
    // Each iteration may trigger a separate SELECT for order.Lines.
    var lineCount = order.Lines.Count;
}

Add eager fetching to load orders, customers, and lines in one round trip:

var orders = session.Query()
    .Fetch(o => o.Customer)          // many-to-one: join Customer
    .FetchMany(o => o.Lines)         // collection: join OrderLine
    .Where(o => o.Date >= startDate)
    .ToList();

NHibernate translates the LINQ call into a SQL statement similar to this shape:

select
    order0_.Id as Id1_0_,
    order0_.CustomerId as Customer...,
    customer1_.Id as Id2_1_,
    orderline2_.Id as Id3_2_
from Orders order0_
inner join Customers customer1_ on order0_.CustomerId = customer1_.Id
left join OrderLines orderline2_ on orderline2_.OrderId = order0_.Id
where order0_.Date >= @p0

The exact join type and alias names depend on your mapping and NHibernate version. The important check is that the log contains one SELECT with joins, not one SELECT per order row.

Nested associations

To continue an eager path after a collection, use ThenFetch or ThenFetchMany. For example, to load each order's lines and each line's product:

var orders = session.Query()
    .FetchMany(o => o.Lines)
    .ThenFetch(l => l.Product)
    .ToList();

Limits and common mistakes

• Multiple collections can duplicate rows. Calling FetchMany on two independent collections in the same query can produce a cartesian product. NHibernate may return duplicated parent rows or split the work into multiple queries, depending on version and configuration. Prefer one collection per eager query or load the second collection separately.
• Fetch is not a mapping change. It applies only to the query where it is declared. Unrelated queries still use lazy loading according to the mapping.
• NHibernate may ignore Fetch. Certain mapping shapes, such as some component or many-to-many configurations, can prevent a join. In those cases NHibernate falls back to its default loading behavior.
• Memory and driver pressure. A wide joined result set hydrates the full object graph in memory. Large page sizes or many columns can pressure the ADO.NET driver and NHibernate hydrator more than several smaller lazy queries.

How to verify the result

1. Enable NHibernate SQL logging for your application. This is typically configured in the application's logging framework (for example, log4net, NLog, or Serilog) by enabling the NHibernate.SQL category.
2. Run the query without Fetch/FetchMany and count the SELECT statements using a database profiler or NHibernate.Stat.
3. Run the same query with eager fetching and confirm the profiler shows one SELECT containing JOIN clauses rather than repeated SELECTs for each parent row.
4. Repeat the test with two FetchMany calls to observe whether your installed NHibernate version duplicates rows or splits the query.

There is no rollback step because eager fetching changes query execution only; it does not insert, update, or delete data.

When to use it

Use Fetch and FetchMany when you know a query will need related data and you can measure that lazy loading is causing excess round trips. Avoid applying it globally; profile first, then add eager fetching to the specific queries that need it.