How do I get random rows using nhibernate?

Question

How do I select 5 random records using nhibernate.

My sql looks like this:

SELECT TOP 5 u.UserId, u.UserName, p.ImageFileName FROM users as u, profiles as p WHERE u.UserId = p.UserId ORDER BY NEWID()

I tried doing this but it doesn't work

IList<User> users = session
                        .CreateCriteria(typeof(User))
                        .CreateCriteria("Profile")
                        .Add<Profile>(p => p.ImageFileName != string.Empty)
                        .AddOrder(Order.Asc("NEWID()"))
                        .SetMaxResults(5)
                        .List<User>();

Answer 1

You can use an SQLQuery:

var query = 
    "SELECT TOP 5 u.UserId, u.UserName, p.ImageFileName " +
    "FROM users as u, profiles as p " +
    "WHERE u.UserId = p.UserId ORDER BY NEWID()";

ISQLQuery qry = session.CreateSQLQuery(query).AddEntity(typeof(User));
List<User> rndUsers = qry.List<User>();