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?

I think that's the best way to do it. In SQL you'd write: SELECT e.* from e WHERE e.Property = (SELECT MAX(e2.Property) WHERE e2.EnquiryCode = e.EnquiryCode) and that's all you're doing in your HQL. — kͩeͣmͮpͥ ͩ, Dec 12 '08 at 15:19
Thanks, this helped me with a similar issue (even though you have no answers!) — PandaWood, Mar 18 '11 at 06:18

score 1 · Answer 1 · edited May 23 '17 at 11:56

1

You should be able to do this with projection:

session.CreateCriteria(typeof(Customer))
  .SetProjection( Projections.Max("Id") )
  .UniqueResult();

As described Nhibernate Criteria: 'select max(id)...'

edited May 23 '17 at 11:56

Community

1
1

answered Aug 28 '12 at 01:29

eepzable

153
2
7

score 1 · Answer 2 · answered Dec 01 '11 at 18:58

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

score 0 · Answer 3 · answered Jul 19 '12 at 16:50

I think this must work:

(from e in NHibernateSession().Query<Enquiry>()
    where e.Property == (
    (
        from e2 NHibernateSession().Query<Enquiry>()
        where e2.EnqueryCode == e.EnquiryCode
        select e2.Property).Max()
    )
    select e
).ToList<Enquiry>()

Select object when a property equals Max with NHibernate

3 Answers3