Emad Ibrahim

Selecting 5 random rows in SQL is easy

select top 5 * from users order by newid()

Nhibernate is a different story.  I am new to NHibernate and I both love and hate it.  I hate the steep learning curve and the unobvious ways of doing things.  Anyway, here is how to select 5 random records using nhibernate.

First class a new order class to perform random ordering:

public class RandomOrder : Order
{
    public RandomOrder() : base("", true) { }
    public override SqlString ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery)
    {
        return new SqlString("newid()");
    }
}

Then use the new class in your query:

IList<User> users = session
    .CreateCriteria(typeof(User))
    .AddOrder(new RandomOrder())
    .SetMaxResults(5)
    .List<User>();

Done…