NHibernate iStatelessSession returns duplicate parent instances on eager fetch

Question

I'm trying to get a root entity and eager fetch it's child entities. But because I'm using the IStatelessSession of NHibernate, it returns duplicates of the root entity for each child. Using an ISession, it would be solved with

.TransformUsing(new DistinctRootEntityResultTransformer()) 

But for an IStatelessSession it's not.

Basically it's about the code below, where there's just one instance of Parent, holding 3 Childs.

var result = session.QueryOver<Parent>()
.Fetch(i => i.Childs).Eager();

This will return 3 duplicate instances of Parent, instead of just one. Does anyone have a solution for this?

Answer 1

I would say: Do not use StatelessSession. It does not suite to this use case.

13.2. The StatelessSession interface

Alternatively, NHibernate provides a command-oriented API that may be used for streaming data to and from the database in the form of detached objects. A IStatelessSession has no persistence context associated with it and does not provide many of the higher-level life cycle semantics. In particular, a stateless session does not implement a first-level cache nor interact with any second-level or query cache. It does not implement transactional write-behind or automatic dirty checking. Operations performed using a stateless session do not ever cascade to associated instances. Collections are ignored by a stateless session. Operations performed via a stateless session bypass NHibernate's event model and interceptors...

I just tried explain that here: NHibernate: Select one to Many Left Join - Take X latest from Parent, The problem here is, that your JOIN is resulting in this SQL result, which is not suitable for paging (which you will need sooner or later)

PARENT1 CHILD1
PARENT1 CHILD2
PARENT1 CHILD3
PARENT2 CHILD4
PARENT2 CHILD5 // if we would take 5 records, the parent2 won't get child6
PARENT2 CHILD6

So this resultset is not the way to go. I would strongly suggest: use

• standard session, isnide of using (to let it dispose immediately)
• load the list of root entity (Parent) and
• let NHibernate load their children lazily - in separated SQL query.

The query could/should be like this:

ISessionFactory factory = ...;
using (var session = factory.OpenSession())
{
    var list = session.QueryOver<Parent>()
    .Skip(100)
    .Take(25)
    .List<Parent>();

    list.Last() // this will load all occupations at once
        .Childs // if batch-size is higher than page size
        .Any(); // otherwise touch more items

} // session is closed and disposed

As the above code snippet shows, to avoid 1 + N issue, we have to use one of the smart mapping features:

19.1.5. Using batch fetching

NHibernate can make efficient use of batch fetching, that is, NHibernate can load several uninitialized proxies if one proxy is accessed (or collections. Batch fetching is an optimization of the lazy select fetching strategy. There are two ways you can tune batch fetching: on the class and the collection level.

Batch fetching for classes/entities is easier to understand. Imagine you have the following situation at runtime: You have 25 Cat instances loaded in an ISession, each Cat has a reference to its Owner, a Person. The Person class is mapped with a proxy, lazy="true". If you now iterate through all cats and call cat.Owner on each, NHibernate will by default execute 25 SELECT statements, to retrieve the proxied owners...

And the parent mapping should be like:

HasMany(x => x.Childs)
    ...
    .BatchSize(100) // should be at least 25

Please, check also these:

• How to Eager Load Associations without duplication in NHibernate?
• NHibernate QueryOver with Fetch resulting multiple sql queries and db hits
• Is this the right way to eager load child collections in NHibernate

NOTE: Some people could suggest to you to use Result Transforemer, as you've tried. This solution could work, but is done in C#, in memory, so all data are loaded (multi lines) and then narrowed. I would never use that. Check: Criteria.DISTINCT_ROOT_ENTITY vs Projections.distinct