Monday 12 September 2011

NHibernate QueryOver and NewId or Rand

Returning a random record using NHibernate is quite simple to achieve. If you do not have the whole collection in memory then you ought to consider using NEWID() (for MSSQL) or RAND() (for MySql)

The following MSSQL query will return 5 random Testimonials:-
select TOP 5 * from Testimonials order by newid()
So how would you do this using NHibernate? The following ICriteria example shows how you would register the MySql function RAND(). If you are using MSSQL then you just need to replace the word RAND with NewId.
public class RandomOrder : Order {
  public RandomOrder() : base("", true) { }
  public override SqlString ToSqlString(
      ICriteria criteria, ICriteriaQuery criteriaQuery) {
    return new SqlString("RAND()");
  }
}
The following code based on ICritrtia returns a random list of Testimonials:-
public IList<CmsTestimonial> GetRandomTestimonials(int count) {
  ICriteria criteria = Session
    .CreateCriteria(typeof(CmsTestimonial))
    .AddOrder(new RandomOrder())
    .SetMaxResults(count);
    return criteria.List<CmsTestimonial>();
}
The following code returns a single random Testimonial:-
public CmsTestimonial GetRandomTestimonial() {
  ICriteria criteria = Session
    .CreateCriteria(typeof(CmsTestimonial))
    .AddOrder(new RandomOrder())
    .SetMaxResults(1);
    return criteria.UniqueResult<CmsTestimonial>();
}

Can I do this using QueryOver?

Ok all good so far but what about if we want to use QueryOver rather than ICriteria?

Make sure you keep the RandomOrder class and then create this extension method:-
public static class NHibernateExtensions {
  public static IQueryOver<TRoot, TSubType> 
      OrderByRandom<TRoot, TSubType>(
        this IQueryOver<TRoot, TSubType> query) {
    query.UnderlyingCriteria.AddOrder(new RandomOrder());
    return query;
  }
}
and change your ICriteria query to the following QueryOver query
public IList<CmsTestimonial> GetRandomTestimonials(int count) {
  return Session
    .QueryOver<CmsTestimonial>()
    .OrderByRandom()
    .Take(count)
    .List();
}
NHibernate has so many extensible points and really mostly makes life easy us developers! This is a real sweet spot.

5 comments:

  1. Interesting post, how would you do this with Linq? I have a complex query using IQueryable and don't really want to have to rewrite it.

    ReplyDelete
  2. I think you would need to first get a count of records, then get a random number based on this count and do a take(1).Skip(rndNumber)

    ReplyDelete
  3. Oh-right, how would this work if say I want to take the 5 random objects out and not just the one?

    ReplyDelete
  4. Good question, you may want to consider a database view and have a column for newid(). You can then just use .take(5)

    ReplyDelete
  5. I was thinking along the same line, but will have to leave that feature out of my current project till i have more time. Pitty really, enjoying using nhibernate just the lack of documentation means its hard to find a good point of reference, when it comes to these kind of things. Came to your workshop in Southampton which showed me a few little things i didn't know about which was helpful.

    ReplyDelete