CODEWITHSUNDEEP
sqlnhibernate
Emad
Emad

Reputation: 4200

How do I get random rows using nhibernate?

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>();

Upvotes: 2

Views: 1462

Answers (1)

Remi Despres-Smyth
Remi Despres-Smyth

Reputation: 4253

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>();

Upvotes: 1

Related Questions