I'm using NHibernate with MS SQL Server, and currently am retrieving a random item of a class using this method:

First I find the number of rows in the class table which satisfy my search conditions. Standard SQL: "SELECT COUNT(*) FROM table WHERE conditions".

Next I select a random number between 0 and n-1, where n is the number of rows.

Then I request the id column of that row: "SELECT TOP 1 id FROM table WHERE NOT IN (SELECT TOP index-1 id FROM table)". This last would be a lot easier with a LIMIT clause, but MSSQL doesn't support that.

Finally, I use NHibernate to get the item based on the returned ID.

This is two queries, plus what NHibernate does, for each random item I want; somebody please tell me there's a better way!

Once you have the random number you can ask NHibernate to select just that record. You can achieve this by calling
SetFirstResult and SetMaxResults on the IQuery interface. After that when you call IQuery.UniqueResult it will return just one entity (you will have to use the correct Order By)

You should be able to do a Native Sql call, i.e session.CreateSqlQuery with a query like....

SELECT TOP 1 {cat.*}
FROM cats {cat}
ORDER BY NEWID()

The order by NEWID gives you a random row each time, don't know how database effiicent it is though.....

Tim