NHibernate - Order by Max() of Collection property in Detached Criteria

Question

I am trying to order a query by an aggregation of a property of its collection (NHibernate 3.2.0).

Here is the sample:

 CurrentSession.CreateCriteria<Note>()
                    .Add(Subqueries.PropertyIn("Id",  
DetachedCriteria.For<SubNote>()
.SetProjection(Projections.ProjectionList()
.Add(Projections.Alias(Projections.Max("Date"), "maxDate")))
.AddOrder(Order.Desc("maxDate")))
                       .List<Note>();

This doesn't work, it says there is no property "maxDate" in "Note"

What i want is to order Note by the maximum date of its collection of SubNote.

Thanks!

Solution

 var subQuery = DetachedCriteria
    .For<SubNote>("sn")
    .SetProjection(
        Projections.Alias(Projections.Max("Date"), "maxDate"))
        .Add(Restrictions.EqProperty("Note.Id", "n.Id"));



var results = CurrentSession.CreateCriteria<Note>("n")


    .AddOrder(Order.Desc(Projections.SubQuery(subQuery)))
    .List<Note>();

Answer 1

Try this:

var subQuery = DetachedCriteria
    .For<SubNote>("sn")
    .SetProjection(
        Projections.Alias(Projections.Max("Date"), "maxDate"))
    .Add(Restrictions.EqProperty("**sn.COLUMNNAME**", "n.Id"));

var results = CurrentSession.CreateCriteria<Note>("n")
    .Add(Subqueries.Select(subQuery))
    .SetProjection(
        Projections.Alias("n.Id", "Id"))
    .AddOrder(Order.Desc("maxDate")))
    .List<Note>();

Not sure if it is 100% correct but it might make things a little more clear. Make sure you change the text between the (**) to the column name in SubNote that is supposed to match the Id in Note.