Hibernate version: 1.2.0.3001

Our requirement is to use EagerFetch for the Children by firing a separate SELECT query on the child table.

With FetchMode.Eager we are having a left outer join with a parent to retrieve a list of children, this is not the way I would write SQL to do it because in the results we are duplicating all the parent data for each child.

With criteria.SetResultTransformer(CriteriaUtil.DistinctRootEntity), these duplicate results are no doubt eliminated but the fact that it is filtered out after getting duplicates from the db doesn't sound efficient way of tackling the problem (though NHibernate itself does it internally) .

Marking lazy="false" in schema and using FetchMode.Select in the source code gives the desired behaviour. However, I can't switch back to lazy loading on scenarios when we dont need children in different scenario.

Any suggestions on tackling this issue would be highly helpful.

If you want to use a seperate select, why is it important that it is fetched eagerly? Is this because you will be closing the session before you need to access the property?

Yes. You sensed it right. We close the session after retrieval is done and the results are passed back to the client back from the Server. It is the client which uses it.