NHibernate - Select Random Number of Records with LINQ

Question

Does anyone know how to select a random number of records in NHibernate using LINQ.

I hoped I could say something like:

var rand = new Random();
var test = session.Query<Entity>().OrderBy(x => rand.Next()).Take(5).ToList();

However it doesn't like variables in the OrderBy expression. One option is to call ToList before I do the ordering but this grabs the whole record set which is not ideal as it could return thousands of records.

I've also discovered the following (scroll down to the bottom answer):

NHibernate Insert into ... select ... with GUID as PrimaryKey

However I'm not sure how I would call this using LINQ. I'd appreciate it if someone could help. Thanks

Answer 1

This solution is probably not very good because it changes the shape of your entities, but if that's acceptable for your use case...

Based on this post by Ayende it looks like it's pretty easy to map SQL functions to properties on your entities:

http://ayende.com/blog/1720/using-sql-functions-in-nhibernate

Could you add a property mapping like

<property name='Random' formula='NEWID()'/>

for the entity you are targeting? Then you should be able to write a query like

var test = session.Query<Entity>().OrderBy(x => x.Random).Take(5).ToList();