NHibernate OrderBy a correlated subquery

Question

I have a parent which has a collection of property. Each property has a propertyclass and a value. I want to return a list of parent ordered by the value of attached any attached property with a given propertyclass

Let's say the parent represents a car, there exists a property with propertyclass "color". I want to return a list of all cars ordered by the value of the "color" property.

In straight SQL, this would be easy, we could just order by a correlated subquery.

SELECT * FROM [Parent] 
ORDER BY (SELECT [Value] FROM [Property] WHERE [PropertyClass] = 'Color' 
  AND [ParentId] = [Parent].[Id]) 

But I do not know how to accomplish this at all in NHibernate.

I've gotten as far as creating a Subquery projection and setting the order-by of the query using it:

var dc = DetachedCriteria.For<Core.Property>()
  .Add(Restrictions.Eq("PropertyClass", sortByProperty))
  .SetProjection(Projections.Property("Value"));
var query = baseQuery.OrderBy(Projections.SubQuery(dc));

But the order-by clause generated by this misses the correlated subquery part- [ParentId] = [Parent].[Id], thus is invalid sql. How do I make this subquery correlated to the base query?

Answer 1

Figured it out.

Core.Property propAlias = null;

baseQuery.JoinAlias(parent => parent.Properties, () => propAlias)
  .Where(() => propAlias.PropertyClass == sortByProperty);

var query = baseQuery.OrderBy(() => propAlias.Value);