2

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>();
Emad
  • 4,110
  • 5
  • 30
  • 35
  • 1
    Similar to http://stackoverflow.com/questions/729687/how-do-i-select-a-random-row-using-nhibernates-icriteria-api. – Joel Dec 08 '09 at 20:02

1 Answers1

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>();
Remi Despres-Smyth
  • 4,173
  • 3
  • 36
  • 46
  • Mind you, the question referred to by Joel Potter in a comment has a more elegant answer. – Remi Despres-Smyth Dec 13 '09 at 03:31
  • Can you use disjunctions with this? I have a similar problem, and thought of that solution, but I need to apply pre-defined Disjunction objects. – Astaar May 05 '12 at 12:33
  • You can supply any SQL you like - this is NHIbernate, but it's just creating an SQL query that will be run by the database. If you don't order by something random, however, you will always get the same records returned. – Remi Despres-Smyth May 05 '12 at 16:45
  • Great! This method does fine. Simple too. – Venugopal M Dec 05 '14 at 06:28