R – Nhibernate criteria query – ordering a collection

criteriaicriterianhibernate

I have a Person class. A person class contains a collection of Friends (also Person objects). A person class also has a LatestLogin property which is the LatestLogin time.

For a given person, I want to return their first 10 friends ordered by descending LatestLogin.

HQL I can do no problem:
select friends from Person person inner join person.Friends friends where person = :person order by friends.LatestLogin desc

How do I write this in a Criteria Query? I don't want the containing person object, just a List of the person's friends ordered by LatestLogin.

Best Answer

Here it is:

var cachedPosts = Session.CreateCriteria<Person>("main")
                .CreateCriteria("Friends", "f")
                .Add(Restrictions.Eq("f.Id", person.ID))
                .AddOrder(Order.Desc("main.LatestLogin"))
                .List<Person>();

Related Solutions

Sql – ordering by collection count in nhibernate

I ran into this issue and I used Linq to get around it.

            var sort = from n in c
                       orderby q.Answers.Count
                       select n;

I BELIEVE that's the syntax, if someone could verify that would be great I haven't used LINQ too much.

C# – NHibernate Criteria with multiple results

I would presume that the answer lies in prefetching the associations that you want to. That way you can fetch the needed part of your object graph in a single shot. You would do this in criteria queries like so.

ICriteria query = session.CreateCriteria(typeof (Cat))
.SetFetchMode("Mate", FetchMode.Join)
.SetFetchMode("Kittens", FetchMode.Join);

IList<Cat> results = query.List<Cat>();

This will give you back a list of cats with both the Mate and the Kittens prepopulated. You can then navigate to these properties without incurring an N+1 penalty. If you need a more flattened result I'd do it using linq, perhaps like this.

var results = query.List<Cat>()
  .Select((c) => new {mother = c, mate = c.Mate, offspr = c.Kittens});

This will give you back a flattened list of anonymous types with the given properties. This will work if all you need is prefetching the object graph. However if you need to prefetch things such as counts or sums then you will need to examine the Projections and Alias parts of Criteria queries.

One more thing. If you are trying to exactly duplicate your above query, you can do so like this.

ICriteria query = session.CreateCriteria(typeof (Cat), "mother")
.CreateAlias("Mate", "mate", JoinType.InnerJoin)
.CreateAlias("Kittens", "offspr", JoinType.LeftOuterJoin)
.SetResultTransformer(Transformers.AliasToEntityMap);

This will return you basically the same as your hql query, however instead of using an indexed list it will use a dictionary that maps the alias to the entity.

Best Answer

Related Solutions

Sql – ordering by collection count in nhibernate

C# – NHibernate Criteria with multiple results

Related Topic