CODEWITHSUNDEEP
sqlitenhibernate
JacquesB
JacquesB

Reputation: 42669

Database independent random order in NHibernate

I have a query which uses code like:

criteria.AddOrder(
    Order.Asc(
        Projections.SqlFunction(
           new StandardSQLFunction("NEWID"), 
           new NHType.GuidType(), 
           new IProjection[0])));

The purpose is to get a random ordering. I run this against an SQL server, but I would also like to run it against an SQLite, since we use this for testing. SQLite does not support NEWID() but has Random instead. Is it possible to write the code (or configure) such that the same query will work against both databases?

Upvotes: 2

Views: 123

Answers (1)

Andrew Whitaker
Andrew Whitaker

Reputation: 126052

I think the way to do this is to create two custom dialects. Have each one implement a random function differently:

public class MyMsSqlDialect : MsSql2012Dialect
{
    protected override void RegisterFunctions()
    {
        base.RegisterFunctions();
        RegisterFunction("random", new StandardSQLFunction("NEWID", NHibernateUtil.Guid));
    }
}

public class MySqliteDialect : SQLiteDialect
{
    protected override void RegisterFunctions()
    {
        base.RegisterFunctions();
        RegisterFunction("random", new StandardSQLFunction("random", NHibernateUtil.Guid));
    }
}

Now, the following query should work fine in either database:

criteria.AddOrder(
    Order.Asc(
        Projections.SqlFunction("random", NHibernateUtil.Guid)));

Note that this is cheating a bit. random doesn't return a Guid in the Sqlite flavor, but since NHibernate doesn't need that information to do the ORDER BY, nothing should go wrong. I would consider calling it random_order or something to make it clear that this is only for ordering.

Upvotes: 3

Related Questions