How do I select a Random Row using NHibernate's ICriteria API?

Question

Can I select a random row using NHibernate's ICriteria API?

Mauricio Scheffer · Accepted Answer · 2010-09-17T21:34:04.397

14

Just as cundh2o said, it's DBMS-specific. But you can subclass the Order class and define your own custom ordering. For example, for SQL Server:

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

edited Sep 17 '10 at 21:34

answered Apr 08 '09 at 23:16

Mauricio Scheffer

98,863
23
192
275

2.1.2 compatible: public override SqlString ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery) { return new SqlString("newid()"); } – mxmissile Sep 17 '10 at 21:14
1

Use of Newid like this leads to terrible performance on medium-size to large tables though. – UpTheCreek May 02 '11 at 09:27

score 1 · Answer 2 · answered Apr 08 '09 at 12:28

If you are not limited to using ICriteria, I might recommend using HQL instead for selecting a random row, since it may provide more flexibility to use the Random function supplied by your db provider.


IQuery q = NHibernateSession.CreateQuery("your hql statement here")

How do I select a Random Row using NHibernate's ICriteria API?

2 Answers2

Linked