How do you specify a sort criteria for an association in NHibernate?

Question

For entity ParentEntity with a collection of type ChildEntity which contains an Order property of type int, how can the parent entity be retrieved with the child collection sorted by Order specifically through the use of the Criteria API as discussed in section 12.4 of the NHibernate docs here?

I've tried using code similar to the following:

public ParentEntity GetById(int id)
{
    ICriteria criteria = _sessionFactory.GetCurrentSession().CreateCriteria(typeof (ParentEntity));
    criteria.Add(Restrictions.Eq("Id", id))
        .CreateCriteria("Children")
        .AddOrder(Order.Desc("Order"));            
    return (ParentEntity) criteria.List()[0];
}

Unfortunately, this code produces 2 SELECT statements. The first select contains an order by which sorts the associated columns retrieved, but the second does not and this seems to be the one from which the collection is being populated.

Note, I've tried configuring NHibernate to do an outer join fetch without which works as expected without the criteria. That is, it produces two queries without the outer join configured, but only one with the outer join configured. The addition of the added criteria seems to cause an extra query regardless.

Please limit answers to how this can be done using the criteria API or explanations as to why this wouldn't work. I'm aware the sorting can be done through the mapping, but I'm trying to understand what specifically the issue is using the criteria method.

==== EDIT ====

The following is the model and mappings:

public class ParentEntity
{
    public virtual int Id { get; private set; }
    public virtual IList<ChildEntity> Children { get; set; }

    public ParentEntity()
    {
        Children = new List<ChildEntity>();
    }
}

public class ChildEntity
{
    public virtual int Id { get; private set; }
    public virtual ParentEntity Parent { get; private set; }
    public virtual int Order { get; private set; }

    protected ChildEntity()
    {
    }

    public ChildEntity(int order)
    {
        Order = order;
    }
}

public class ParentEntityMap : ClassMap<ParentEntity>
{

    public ParentEntityMap()
    {
        WithTable("Parent");
        Id(p => p.Id);
        HasMany(p => p.Children)
            .KeyColumnNames.Add("Parent_Id")
            .Cascade.All();
    }
}

public class ChildEntityMap : ClassMap<ChildEntity>
{

    public ChildEntityMap()
    {
        WithTable("Child");
        Id(c => c.Id);
        Map(c => c.Order, "[Order]");
        References(c => c.Parent, "Parent_Id")
            .Cascade.All();
    }
}

==== EDIT 2 ====

As an update, after adding Not.LazyLoad() to the Parent only a single SELECT is generated, however, the results are still unsorted.

Answer 1

How did you map the children ? You can only indicate that NHibernate should retrieve the children in an ordered fashion, if you've mapped them as an 'ordered list' (map / list / dictionary).

For instance: http://ayende.com/Blog/archive/2009/06/02/nhibernate-mapping-ltlistgt.aspx

I've you've mapped the Children as an un-ordered list (set / bag), then NHibernate will not be able to sort the Children for you. In such case, you'll have to sort the Children when the user accesses them

public class Parent
{
    private ISet<Child> _children = new HashedSet<Child>();

    public ReadOnlyCollection<Child> Children
    {
        new List<Child>(_children).OrderBy(child => child.SequenceNr).ToList().AsReadOnly();
    }

}

Answer 2

Kinda hard without seeing your model and mapping. But maybe something like this?

 var criteria = session.CreateCriteria<ParentEntity>();
 criteria.Add(Restrictions.Eq("Id", id))
                .CreateAlias("Children", "children")
                .AddOrder(Order.Desc("children.Order"));

Answer 3

From the behavior I'm observing, the problem appears to be that while a constraint can be placed upon an association (as shown in section 12.4 of the docs), such constraints are only relevant to the extent that they serve as a meaningful filter for the root entity. Consider the following example from the docs:

IList cats = sess.CreateCriteria(typeof(Cat))
.Add( Expression.Like("Name", "F%")
.CreateCriteria("Kittens")
    .Add( Expression.Like("Name", "F%") )
.List();

This says give me back all cats where the name starts with "F", but only those cats which have Kittens with a name starting with "F". This does not say return the kittens with names starting with "F". Ordering works in a similar way. We might have asked that the kittens be ordered by name, which NHibernate is happy to pass along as part of the criteria, but such ordering would have no bearing on how the kittens are returned. Therefore, my conclusion is that using the Criteria API can't be used to filter or order the associations returned.

The aforementioned section of the docs does state that the associations returned are not pre-filtered by the criteria, but I didn't quite understand what was meant until I understood what the association criteria was used for.