There is yet another issue with NHibernate and JetDriver I encountered yesterday. It seems that a select query containing “Order By”, will gets its “From” keyword cut off at some stage by the Jet Driver. It means simple object querying won’t work:

public IList<Account> GetAllAccounts()
{
    using (UnitOfWork.Start())
    {
        var repository = new NHRepository<Account>();
        var orders = new[] { new Order("Firstname", true), new Order("Lastname", true) };

        return repository.FindAll(orders).ToList();
    }
}

To fix it, let’s first pin point the problem by creating a failing test. It was a good thing I was already running everything from the trunk build, so I opened up the JetDriver project (from NHContrib) and luckily there is already a test project. Since I didn’t want to actually change any method visibility, I used reflection to call the private methods that fails this test:

[TestFixture]
public class JetOrderByFixture
{
    [Test]
    public void Select_Statement_With_OrderBy_Should_Run()
    {
        SqlString sql = new SqlString("SELECT * FROM ACCOUNTS ORDER BY Firstname ASC, Lastname ASC");
        JetDriver driver = new JetDriver();

        SqlString processedSql = driver.GetType()
                                       .GetMethod("FinalizeJoins", BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.InvokeMethod)
                                       .Invoke(driver, new object[] { sql }) as SqlString;

        Assert.That(processedSql, Is.Not.Null);
        Assert.That(processedSql.LastIndexOfCaseInsensitive("FROM"), Is.GreaterThan(0));
    }
}

The problem is the private FinalizeJoins method, so the test, to no surprise, fails. I have created a task in NHibernate’s Jira and the patch to fix this is attached, if you need it.