Diagnosing and Fixing NHibernate N+1 Select Problems in Lazy-Loaded Collections

Recognizable Condition

When a request loads a parent entity and then accesses a lazy‑loaded collection, the application shows a high number of database round‑trips and slow response times. Enabling NHibernate SQL logging reveals many identical SELECT statements that retrieve child rows for the same parent identifier.

Cause/Diagnostic Table

Symptom	Likely Cause	Check
Many identical SELECTs for the same parent ID	Lazy collection accessed without batch‑size or fetch	Enable SQL logging and look for repeated SELECT statements
Unexpected SELECT after accessing a property	Property access triggers lazy load (e.g., ToString)	Review entity code for inadvertent access
High session statement count	Missing batch‑size or fetch mapping	Review mapping batch‑size attribute

Ordered Checks

1. Enable NHibernate SQL logging (show_sql=true) to capture generated SQL.
2. Use ISession.Statistics to obtain StatementCount and EntityLoadCount.
3. Correlate log entries with request identifiers to see which parent entities trigger extra SELECTs.
4. Verify that the collection mapping has lazy=\"true\" and that no eager fetch (Join/Fetch) is forced elsewhere.
5. Inspect LINQ queries or DTO projections for code that may cause lazy initialization.

Fixes Tied to Findings

If the N+1 pattern is confirmed, apply one of the following remedies:

• Add a batch‑size to the collection mapping:

<bag name=\"Children\" lazy=\"true\" batch-size=\"20\">
    <key column=\"ParentId\" />
    <one-to-many class=\"Child\" />
</bag>

• Change the query to fetch the collection eagerly using one of these APIs:
• Criteria: criteria.SetFetchMode(\"Children\", FetchMode.Join)
• QueryOver: queryOver.Fetch(x => x.Children).Eager
• HQL: \"from Parent p left join fetch p.Children where p.Id = :id\"
• For small, static data, map the collection as eager (lazy=\"false\") – use only when the data set is known to be tiny.
• Enable a second‑level cache to reduce repeated loads of the same child data.

Escalation Criteria

After applying batch‑size or fetch fixes, re‑evaluate:

• If the SELECT count remains greater than five times the expected number.
• If average response time exceeds two seconds under realistic load.

In these cases:

1. Re‑examine mappings for hidden lazy‑load triggers (custom interceptors, event listeners, property accessors).
2. Profile with NHibernate Profiler to detect unexpected eager fetches.
3. Involve the data‑access layer lead or architect for a deeper redesign.

Verification Steps

• Enable show_sql=true in NHibernate configuration, run a representative use case, and count the distinct SELECT statements for a given parent ID in the log output.
• After the operation, call ISession.Statistics and verify that StatementCount matches the expected number (e.g., 1 + number of batches) and that EntityLoadCount reflects the anticipated entity loads.
• Optionally, write a unit test using an in‑memory SQLite database with a SqlStatementInterceptor that asserts the captured SQL contains no more than one SELECT per parent plus batch‑size‑controlled child selects.

Limitations

Behavior of batch‑size and fetch strategies can differ between NHibernate 3.x, 4.x, and 5.x; test changes against the specific version in use. Enabling show_sql=true or using a profiler adds overhead; disable in production unless troubleshooting and ensure logging does not flood disk.

Parent Entity, Lazy Collection, SQL Statements, Batch Size