Complex query in nHibernate using DetachedCriteria

Question

Hello! I'm currently trying to move from hand-crafted hql to queries constructed via DetachedCriteria. I have and HQL:

from GenericObject genericObject 
      left join fetch genericObject.Positions positions
      where (positions.Key.TrackedSourceID, positions.Key.PositionTimestamp) in 
      (select gp.Key.TrackedSourceID, max(gp.Key.PositionTimestamp)
       from GenericPosition gp 
group by gp.Key.TrackedSourceID) 

Now using DetachedCriteria:

var subquery = DetachedCriteria
                .For (typeof (GenericPosition), "gp")
                .SetProjection (Projections.ProjectionList ()
                                    .Add (Projections.Property ("gp.Key.TrackedSourceID"))
                                    .Add (Projections.Max ("gp.Key.PositionTimestamp"))
                                    .Add (Projections.GroupProperty ("gp.Key.TrackedSourceID"))
                );
            var criteriaQuery = DetachedCriteria
                .For (typeof (GenericObject), "genericObject")
                .CreateAlias ("genericObject.Positions", "positions")
                .SetFetchMode ("genericObject.Positions", FetchMode.Eager)
                .Add (Subqueries.In (??, subquery))

I don't know what to type instead of ?? to create expression like (positions.Key.TrackedSourceID, positions.Key.PositionTimestamp)

Answer 1

I cannot see the advantage in moving from hql to DetachedCriteria, if the latter is more difficult to write. And read.

In my projects I use preferrably DetachedCriteria, unless the syntax gets too complicated. Then I use hql. Until it gets complicated again. Then I give it a try in sql, and go back to hql if it doesn't improve readability.

Keep in mind that you will have to maintain these queries in the future.

Answer 2

HQL has lot of string manipulations done internally and hence may create memory issues as string is immutable type. It is advisable to use DetachedCriteria instead of HQL.