NHibernate Queryover - Is There Any Way Of Getting Better SQL Out Of NHibernate When Retrieving This Object Plus Collections Graph?

We have a situation where we are trying to retrieve a couple of levels deep for an object graph using QueryOver. So if our top level is ParentEntity and our children are ChildEnti

Solution 1:

The way I would suggest, is to use full power of NHibernate features in these combinations:

• The first is a complex query.
• The second is to profit from lazy loading and batch-size setting

So, in the first case we are about to create one and only one QueryOver (maybe including some subqueries)

It must contain projections, that's essential. it can include (many) Left, Inner joins, can be deeply filtered... We will then get the narrowed SELECT statement, containing only required fields. This results in one SQL query, which must be converted into some (unmapped) DTO object

The Projections and DTO means, that at the end we have to use ResultTransformer, which will convert all coming selected fields into DTO object properties.

In the second case, we are trying to profit from lazy behaviour and batch-size mapping. It could look like this:

1) class/ entity

<class name="ParentEntity" batch-size="25" lazy="true">
...

2) collection

 <bag name="Children" lazy="true" batch-size="25"
    ...

See more in documentation: 19.1.5. Using batch fetching

What we gain here is, that we can create soft queries returning only light objects (these which were mapped). This is the first Select. Then all the properties (many-to-one, one-to-many) are loaded in batches - but only if they are accessed, needed.

This leads to more than 1 SELECT clause, but also there is no inflation of SELECT clauses like 1 + N. If NHibernate will find out, that some of the required properties is already loaded in the session... it won't fire the SELECT any more.

Summary: both approaches are really the way. Try to play arround to find out in which situation you get more from the first or the second. BUT the mapping with lazy="true" and batch-size="25" should be used anyway

Some more links about batch-size:

• 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