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.