Select object when a property equals Max with NHibernate

Question

We have a query that selects rows depending on the value of another, ie. the max. I don't think that really makes much sense, so here is the query:

var deatched = DetachedCriteria.For<Enquiry>("e2")
   .SetProjection(Projections.Alias(Projections.Max("Property"), "maxProperty"))
   .Add(Restrictions.EqProperty("e2.EnquiryCode", "e.EnquiryCode"));

session.CreateCriteria(typeof(Enquiry), "e")
   .Add(Subqueries.PropertyEq("Property", deatched))
   .AddOrder(Order.Asc("EnquiryCode"));

My question is, is this the best way? Can anyone suggest a better way?

Answer 1

For aggregations it is better to use SQL and not HQL.Use Nhibernate just for main Entities and their relations (very maintainable design) .Stored procedures are a better place to these aggregations and functions because they are data dependent and not object dependent