Linq to NHibernate, Order by Rand() ?

Question

Hi everybody, I'm using Linq To Nhibernate, and with a HQL statement I can do something like this:

string hql = "from Entity e order by rand()";

Andi t will be ordered so random, and I'd link to know How can I do the same statement with Linq to Nhibernate ?

I try this:

var result = from e in Session.Linq<Entity> 
             orderby new Random().Next(0,100) 
             select e;

but it throws a exception and doesn't work...

is there any other way or solution?

Thanks

Cheers

Answer 1

I guess Linq to NHibernate is unable to convert the Random.Next call to SQL...

An option would be to sort the results after you retrieve them from the DB :

var rand = new Random();
var query =  from e in Session.Linq<Entity> 
             select e;
var result = from e in query.AsEnumerable()
             orderby rand.Next(0,100) 
             select e;

Note that you need to use a single instance of Random, because the seed is based on the current number of ticks ; if you create several instances of Random with very short interval, they will have the same seed, and generate the same sequence.

Anyway, sorting a collection based on a random number is not such a good idea, because the sort won't be stable and could theoretically last forever. If you need to shuffle the results, you can use the Fisher-Yates algorithm :

var result = Session.Linq<Entity>().ToArray();
var rand = new Random();
for(int i = result.Length - 1; i > 0; i--)
{
    int swapIndex = rand.Next(i + 1);
    var tmp = result[i];
    result[i] = result[swapIndex];
    result[swapIndex] = tmp;
}