NHibernate.Extensions.AsSplitQuery

Prevent cartesian explosion in NHibernate LINQ queries when eager loading multiple collections.

Similar to Entity Framework Core's AsSplitQuery(), this library provides an extension method that splits collection loading into separate database queries.

• Prevents cartesian product explosion — no more multiplicative row duplication
• EF Core-like API — familiar AsSplitQuery() syntax
• Genuinely asynchronous — ToListAsync(), FirstAsync() and friends issue every round trip through NHibernate's async path
• Batched child queries — a large root result set never produces an oversized IN clause
• LINQ integration — works with Where(), OrderBy(), Skip(), Take()
• Single entity support — First(), FirstOrDefault(), Single(), SingleOrDefault() and their async variants
• Fails loudly on mappings it cannot reproduce — see What can be split

dotnet add package NHibernate.Extensions.AsSplitQuery

Or via Package Manager:

Install-Package NHibernate.Extensions.AsSplitQuery

using NHibernate.Extensions.AsSplitQuery;

// Instead of this (cartesian explosion):
var orders = await session.Query<Order>()
    .FetchMany(o => o.OrderItems)      // N x M rows
    .ThenFetchMany(i => i.Tags)        // N x M x P rows
    .ToListAsync();

// Use this (split queries):
var orders = await session.Query<Order>()
    .FetchMany(o => o.OrderItems)
    .ThenFetchMany(i => i.Tags)
    .AsSplitQuery()
    .ToListAsync();

Result:

• Before: one query returning the full cartesian product
• After: three queries returning only the rows that exist
  1. SELECT * FROM Orders
  2. SELECT * FROM OrderItems WHERE OrderId IN (...)
  3. SELECT * FROM Tags WHERE OrderItemId IN (...)

var recentOrders = await session.Query<Order>()
    .Where(o => o.OrderDate > DateTime.Now.AddMonths(-1))
    .OrderBy(o => o.OrderDate)
    .FetchMany(o => o.OrderItems)
    .ThenFetchMany(i => i.Tags)
    .FetchMany(o => o.Shipments)
    .AsSplitQuery()
    .Skip(20)
    .Take(10)
    .ToListAsync();

Paging is applied to the root query alone, so Skip/Take count orders rather than joined rows.

var customer = await session.Query<Customer>()
    .Where(c => c.Id == customerId)
    .FetchMany(c => c.Orders)
    .ThenFetchMany(o => o.OrderItems)
    .FetchMany(c => c.Addresses)
    .AsSplitQuery()
    .FirstAsync();

Fetch() and ThenFetch() on a many-to-one reference are left in the root query: a single-valued join multiplies no rows, so there is nothing to split and no extra round trip is spent.

var items = await session.Query<OrderItem>()
    .Fetch(i => i.Order)                 // stays a join in the root query
    .ThenFetchMany(o => o.Shipments)     // split into its own query
    .AsSplitQuery()
    .ToListAsync();

1. Analyzes the LINQ expression tree to find every FetchMany / ThenFetchMany
2. Strips those collection fetches from the root query, leaving reference fetches in place
3. Executes the root query to get the primary entities
4. Executes one query per collection level, filtered by the parent identifiers in batches
5. Hydrates the collections and registers them with the session exactly as NHibernate's own collection loader does, snapshot included, so the session still flushes cleanly

The saving is structural: a join fetch transfers the product of the collection sizes, a split query transfers their sum.

Row count is not the same as elapsed time: a split query trades one round trip for several, so the win grows with collection width and shrinks with network latency. Measure your own workload.

No configuration is required. One knob is available for the child queries:

// Maximum number of parent identifiers per child query IN clause. Default: 500.
AsSplitQueryOptions.ParentIdBatchSize = 500;

The default keeps every statement inside SQL Server's 2100-parameter limit and Oracle's 1000-item IN list, and keeps the number of distinct statement shapes small so the server's plan cache is not flooded with one plan per result-set size.

• NHibernate: 5.5.2 or higher
• .NET: 8.0
• Databases: all NHibernate-supported databases

A split query rebuilds a collection with session.Query<TChild>().Where(c => ids.Contains(c.Parent.Id)). That statement carries none of the mapping's own restrictions, so the library refuses — with a NotSupportedException naming the collection and the reason — any mapping whose result it could not reproduce faithfully. It never returns a collection that quietly differs from what a join fetch would have produced.

Supported

• One-to-many collections mapped as set (ISet<T>), bag or list (IList<T>)
• Bidirectional associations: the child must expose the many-to-one reference back to the parent
• Single-column foreign keys
• Any depth of ThenFetchMany, and any number of sibling FetchMany calls

Refused with a clear exception

Not split, by design

• Fetch() / ThenFetch() on a many-to-one — kept in the root query
• Queries returning scalars or projections (Count(), Any(), Select(...)) — executed directly

• Collections already loaded in the session are left alone. Their contents, including unflushed changes, are authoritative; the split query hydrates only the parents that still need it.
• Transactions: no special handling needed. Hydration adds nothing to the flush.
• Thread safety: the reflection caches are concurrent. An ISession itself is not thread-safe, and this library does not change that.
• A collection under a reference fetch (Fetch(x => x.Ref).ThenFetchMany(r => r.Items)) is split normally; the reference targets loaded by the root query are used as the parents.

Integration tests run against real NHibernate on an in-memory SQLite database, with lazy collection mappings so the split path is genuinely exercised, and an interceptor asserting the statements actually issued.

dotnet test

Covered: split execution and nested collections; multiple fetch paths; Where/OrderBy/Skip/Take; single-entity queries sync and async; empty collections; parent-id batching; scalar and projection queries; reference fetches; transaction safety, dirty checking and rollback; flush cleanliness after hydration; already-loaded collections; every refused mapping.

Contributions are welcome. Please feel free to submit a Pull Request.

This project is licensed under the MIT License - see the LICENSE file for details.

• Inspired by Entity Framework Core's AsSplitQuery() feature
• Built for the NHibernate community

• Report a bug
• Request a feature

Made by CArnaboldi