If you’ve not checked out NHibernate 3’s QueryOver syntax, I highly recommend you do so. When the queries start getting complicated, I find it even more readable than LINQ. Plus, since you can create detached criteria with QueryOver, it opens the door for even more query-piece reuse. That can help keep your code dry.
For those who don’t know, the QueryOver syntax uses lambda expressions to apply strong typing to the NHibernate Criteria syntax. Which uses strings to get its work done. I guess that makes it just another abstraction (QueryOver) on top of an abstraction (Criteria) on top of an abstraction (ADO.NET). Besides, any morning that starts with downloading the NHibernate source is going to be a good morning, right?
All of the examples I’ve found have retrieved the entire collection and performed the randomization in memory. I suppose that’s fine if you have a small data set. I am not that fortunate; my data set is quite large. I need to do the sort in SQL server. Don’t worry. This one is going to be really simple.
First, we need to define a RandomOrder for our system. I’m using SQL Server. Your syntax may be different, depending on your database platform. The good news is that I didn’t have to do this on my own.
public class RandomOrder : Order
{
public RandomOrder() : base("", true) { }
public override SqlString ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery)
{
return new SqlString("NEWID()");
}
}
If we’re using the Criteria API, then we’re done. But we’re using QueryOver, so I need to wrap this with a class that will invoke the ordering from a QueryOver.
public class QueryOverRandomOrderBuilder<TRoot, TSubType>
{
protected IQueryOver root;
public QueryOverRandomOrderBuilder(IQueryOver root)
{
this.root = root;
}
private void ApplyRandomOrder()
{
this.root.UnderlyingCriteria.AddOrder(new RandomOrder());
}
public IQueryOver<TRoot, TSubType> Order
{
get
{
ApplyRandomOrder();
return this.root;
}
}
}
This is almost identical to how the NHibernate source code applies the normal ordering operators. Only instead of a specific string, they process the lambda expression to determine the path. They then use the same root.UnderlyingCriteria.AddOrder() method to apply the order clause to the Criteria query. Neat, huh?
Only one more thing to do, and that’s to write an extension method on top of QueryOver to let us get to these classes from our QueryOver.
public static class QueryOverHelpers
{
public static IQueryOver<TRoot, TSubType> OrderByRandom<TRoot, TSubType>(this IQueryOver<TRoot, TSubType> root)
{
var builder = new QueryOverRandomOrderBuilder<TRoot, TSubType>(root);
return builder.Order;
}
}
That’s it. Now from our code, we can do something like this.
session.QueryOver.Where(c => c.IsActive) .OrderByRandom() .Take(10) .List()