Nhibernate Queryover with Fetch Resulting Multiple SQL Queries and Db Hits

NHibernate QueryOver with Fetch resulting multiple sql queries and db hits

I would say, that this behaviour is what we should expect. Let's have a scenario, in which we have in the system 2 users and 2 roles

User1 - Role1 // has only Role1
User2 - Role1 // now we see that Role2 has more then User1
User2 - Role2

Let's say, that the first query, will retrieve only User1 and its many-to-many relation Role1. What we have in the ISession at the moment is only User1, so the set of Users for Role1 is incomplete (we cannot reuse objects loaded into ISession at the moment). But how should one know where we are? That all data loaded for Role1 is or is not in the session?

New query, loading the data for Role1 must be issued. And this way, we can at the end have dosens of these queries...

What I see as the best solution (I am using it in almost all scenarios) is the batch-size setting: 19.1.5. Using batch fetching

HasManyToMany(x => x.UsersInRole)
  ...
  .BatchSize(25)

Mark all your collection maps with .BatchSize(25) and do that even for the Class map as well. That will cause more then 1 SQL Script, but at the end not more then 1 + (2-4) dependent on the batch-size and page size.

NHibernate. QueryOver Take(n) - with Left.Join

The point here is a difference in our need of paging and its implementation.

while we would expect 5 root elements to be returned,
the result is converted into 5 rows, 5 selected rows :

Some clue could be found in this Q & A: NHibernate QueryOver with Fetch resulting multiple sql queries and db hits

In case of paging with SQL Server 2012 dialect, we would see SQL like this:

SELECT ...
FROM root
JOIN collection
WHERE....
OFFSET 0 ROWS -- skip 0 or 10 or 20 rows
FETCH NEXT 5 ROWS ONLY; -- take 5 rows

So it could at the end result in returning just ONE root entity, if amount of chidren (comments) is 5+

Solution? I would suggest to do it by:

select just root entity
use batch fetching to load children

Selecting just root, could eve mean select also any many-to-one/Reference. This will end up in a star schema structure, which with left joins will again correctly page over the root entity.

Batch fetching is described here in doc:

19.1.5. Using batch fetching

Some similar issues:

NHibernate: Select one to Many Left Join - Take X latest from Parent
How to Eager Load Associations without duplication in NHibernate?

NHibernate Nested List Query

Yes NHibernate supports this functionality, it is called:

19.1.5. Using batch fetching

Let me cite from docs:

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. You can tune this behavior by specifying a batch-size in the mapping of Person:

<class name="Person" batch-size="10">...</class>

NHibernate will now execute only three queries, the pattern is 10, 10, 5. ...

That setting batch-size="25" could be used also for collection mapping:

You may also enable batch fetching of collections. For example, if each Person has a lazy collection of Cats, and 10 persons are currently loaded in the ISesssion, iterating through all persons will generate 10 SELECTs, one for every call to person.Cats. If you enable batch fetching for the Cats collection in the mapping of Person, NHibernate can pre-fetch collections:

<class name="Person">
    <set name="Cats" batch-size="3">
        ...
    </set>
</class>

I would strongly suggest to use it. I do have that mapping on every class and collection

Nhibernate query with Distinct

There are in fact two approaches:

1) Use the ex post Distinct result transformer:

criteria.SetResultTransformer(NHibernate.Transform.Transformers.DistinctRootEntity);

But I would strongly suggest: do NOT go this way. You'll never be able to apply the paging.

2) Do not JOIN the collections, use BATCH loading for (all) collections

So, instead of JOINing the collections and creating the cartesion product - let's use batch loading:

19.1.5. Using batch fetching

Small cites:

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. You can tune this behavior by specifying a batch-size in the mapping of Person:

<class name="Person" batch-size="10">...</class>

or for a collection:

<class name="Person">
    <set name="Cats" batch-size="3">
        ...
    </set>
</class>

Please, for more details, DO observe these:

NHibernate QueryOver with Fetch resulting multiple sql queries and db hits
Is this the right way to eager load child collections in NHibernate
https://stackoverflow.com/questions/18419988/

NHibernate: Select one to Many Left Join - Take X latest from Parent

The way I would go here is:

load the list of root entity (Parent) and
let NHibernate load their children lazily - in separated SQL query.

To avoid 1 + N issue, we can use smart mapping feature:

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...

So, the query should be like this:

session.QueryOver<Parent>()
    .Where(x => x.TimeStamp > from)
    .And(x => x.TimeStamp < to).OrderBy(x => x.TimeStamp).Desc
    //.Left.JoinQueryOver<Child>(x => x.Childs)
    // .TransformUsing(new DistinctRootEntityResultTransformer())
    .Skip(start) // paging
    .Take(maxCapacity)
    .List<Parent>();

And the parent mapping should be like:

<class name="Parent">
    ...
    <bag name="Childs" batch-size="3">
        ...
    </bag>
</class>

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

NHibernate iStatelessSession returns duplicate parent instances on eager fetch

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

Nhibernate Queryover with Fetch Resulting Multiple SQL Queries and Db Hits