NHibernate ICriteria - order by child collection count ?

Question

Hi,

Here is a simple example scenario -

A 'Tag' has many 'Questions'. When I get a list of Tags how do I order by the Tags Question Count using the Criteria API?

I have done this before but haven't touched NH in about 4 months and have forgotten... projections maybe? help!!!

Thanks

Answer 1

Try:

var tags = Session.CreateCriteria(typeof(Tag))
                  .AddOrder(Order.Asc("Tag.Question.Id")
                  .List<Tag>();

// If that does not work, try:
var tags = Session.CreateCriteria(typeof(Tag))
                  .CreateCriteria("Question", "TagQuestion", JoinType.InnerJoin)
                  .AddOrder(Order.Asc("TagQuestion.Id")
                  .List<Tag>();

1. Ordering on joined columns.
2. Ordering the query criteria.

Edit: Unless you're deciding against it or your already comfortable with the Criteria API, you should take a look at either HQL or NHibernate.Linq:

var tags = Session.Linq<Tag>()
               .OrderBy(tag => tag.Question.Id)
               .ToList();

Linq to NHibernate: a vast improvement.

Answer 2

Sat down with a fresh pair of eyes and figured it out... Tags are now ordered by a propery on the Tags Question collection (views).. which made alot more sence in my domain than ordering by the count of children

    public IList<Tag> GetTop(int numberOfTags)
    {
        using (ITransaction transaction = Session.BeginTransaction())
        {

            DetachedCriteria detachedCriteria = DetachedCriteria.For<Tag>()
                    .CreateCriteria<Tag>(x => x.Questions)
                    .AddOrder<Question>(x => x.Views, Order.Desc)
                    .SetMaxResults(numberOfTags)
                    .SetProjection(Projections.Distinct(Projections.Id()));

            IList<Tag> tags = Session.CreateCriteria<Tag>()
                .SetFetchMode<Tag>(x => x.Questions,FetchMode.Join)
                .Add(LambdaSubquery.Property<Tag>(x => x.Id).In(detachedCriteria))
                .SetResultTransformer(new DistinctRootEntityResultTransformer())
                .List<Tag>();

            transaction.Commit();
            return tags;
        }
    }