nHibernate ICriteria order by many to many property min value

Question

How do I write a ICriteria that matches this linq?

ProgramItems.OrderBy(x => x.TimeBlocks.Min(y => y.StartTime))

ProgramItem - TimeBlock has a many to many relationship.

I can filter by a TimeBlock id with this code:

criteria.Add(Subqueries.Exists(DetachedCriteria.For<ProgramItem>("p")
        .CreateAlias("p.TimeBlocks", "timeBlocks")
        .Add(Restrictions.EqProperty("p.Id", "ProgramItem.Id"))
        .Add(Restrictions.Eq("timeBlocks.Id", request.TimeBlockId))
        .SetProjection(Projections.GroupProperty("p.Id"))
        .Add(Restrictions.Eq(Projections.Count("p.Id"), 1))));

But I can't find a way to use the alias/projection to order by a property in TimeBlock - any clue?

Answer 1

The way how to ORDER BY via many-to-many end would look like this:

var subquery = DetachedCriteria.For<TimeBlock>("timeBlock")
    .CreateAlias("timeBlock.ProgramItems", "programItems")
    .SetProjection(Projections.Min("timeBlock.StartTime"))
    .Add(Restrictions.EqProperty("programItems.Id", "root.Id"));

// ORDER BY built from Subquery
var orderByMin = new Order(Projections.SubQuery(subquery), true); // true is ASC

var list = session
    .CreateCriteria<ProgramItem>("root")
    .AddOrder(orderByMin)
    .SetMaxResults(10)    // paging... if needed
    .List<ProgramItems>();

As we can see, both ends of many-to-many must be mapped. We are then building the MIN part over the TimeBlock entity - with a JOIN to the ProgramItem table. That is used in a root query as a way how to filter.

My suggestion would be to change many-to-many to have explicit entity for a pairing table. That would lead to much more simplified queries... but in this case we can do it even with this