Testing NHibernate queries using IEnumerable

NHibernate has several ways to query a database, the easiest one to use is through a Linq provider. I don’t like other ways very much:

  • HQL (Hibernate Query Language)  – You have to write a string with no type checking, e.g. “select Name from Cat”.
  • Criteria API – Uses magic string, rather awkward for more complex queries.
  • QueryOver – It doesn’t use magic strings like criteria API, but I find aliases variables disgusting, plus making more complex queries (e.g. multiple sub-queries) rather difficult and unfriendly.
  • Sql query – Just plain SQL, IMO best choice when linq can’t do the job.

NHibernate Linq

The NHibernate Linq provider is great, you can search through entities using a Linq, everything is statically checked, the intent is clear. NHibernate is using IQueryable, not IEnumerable, the difference is that IQueryable stores the info about expressions used for search and these expressions are later used to create a SQL query that hits the database. The IEnumerable always pulls object from previous method in the chain thus any filtering is done not in the database, but in the memory.

var oldCatNames = 
  from cat in session.Query<Cat>()
  where cat.Age >= 12
  select cat.Name;
return View(oldCatNames.ToArray())

The example of a NHibernate Linq query getting all cats that are old. NHibernate generates a SQL statement, executed it and transforms the result into an array of names. The key question is how to we test such queries?

We can

  1. Have our production DBMS and each test will have to fill in the data and run the query against the database. I am doing it in my project with Oracle and TBH it is rather slow (you have to connect to the db for each test – several seconds), you have to clear/fill in a lot of data as required by constraints (most of the time the referenced data are not not required by the test) and although it has a merit (e.g. when testing stored procedures, more complex queries and so on), for simple queries (=most queries) it seems like a overkill.
  2. Have a in-memory DBMS (e.g. SQLite) and run tests against it. I am doing it for my playground project, but IMO it is even worse than the first proposition, the only benefit is speed and drawbacks are significant. You still have to fill the database and the engine is different that from the production one. For example, sequences are not supported by SQLite. I am using them in my mapping files, so now what? What about stored procedures? SQLite has lousy implementation of time functions and so on.
  3. Use IEnumerable instead of IQueryable and run tests in memory, w/o any DBMS at all.

I am going to explore third option, because it will correctly test most Linq queries for quite a little code.

SQL semantic vs IEnumerable semantic

Before we dive into how to actually do it, there is an important thing to remember:

Result of SQL query and IEnumerable query may be different, although it looks exactly same in the code.

The NHibernate (and Entity Framework) are using SQL semantic that sometimes differ from IEnumerable sematics, the most obvious case are aggregation methods such as Sum. Let us consider following query that is getting total amount of all conjectures:

int sum = session.Query<Conjecture>()
  .Sum(conjecture => conjecture.Amount);

What is the result, when the table for entity Conjecture is empty? No, it is not 0, it is a GenericADOException. The reason is SQL semantic. The NHibernate will infer from the conjecture.Amount that result of query should be an int. It will constructs the query and tries to cast the result into an int. But the result of SQL query (select cast(sum(conjecture0_.Amount) as INT) as col_0_0_ from Conjecture conjecture0_) on empty table is not a 0, but null per definition of SUM in SQL. Thus the exception.

This is intended result per bug NH-3113. In order to get zero, we have to change type of infered result and return 0, when result is null:

int sum = session.Query<Conjecture>()
  .Sum(conjecture => (int?)conjecture.Amount) ?? 0;

When using IEnumerable masquerading as IQueryable for tests, we must be aware of the difference.

Testing query

Query is not a method of ISession, but an extension method of NHibernate.Linq.LinqExtensionMethods class and testing extension methods in C# is painful – they are basically static methods called on an instance. The obvious solution is to use your own facade that hides the NHibernate ISession so you are using your own interfaces that isolate you from quirks such as this one.

If you are using facade, it is really simple to mock result of query, just take any IEnumerable and use extension method AsQueryable from Queryable class (use better name than ISessionFacade):

Conjecture[] conjectures = new[] 
{
  new Conjecture("Minor work", 10),
  new Conjecture("Bug fix", 50),
  new Conjecture("Simple feature", 100),
  new Conjecture("Complicated feature", 500),
};
var sessionFacade = Mock.Of<ISessionFacade>();
sessionFacade.Setup(x => x.Query<Conjecture>())
  .Returns(conjectures.AsQueryable())
// Here would be tested method, I am inlining
var largeConjectureNames =
                from conjecture in sessionFacade.Query<Conjecture>()
                where conjecture.Amount >= 100
                select conjecture.Name;
var expected = new[] { "Simple feature", "Complicated feature" };
CollectionAssert.AreEqual(expected, largeConjectureNames.ToArray());

If you are using ISession from NHibernate and Query extension method from NHibernate.Linq for your queries, you either have to replace the ISession with a facade or mock the Query extension method. I am mocking the extension method, because our project is not using sane DI system (my next task).

Mocking Query method

Let me start by saying this: Mocking extension method is horrible.

Extension methods have their place, e.g. string object doesn’t have a Truncate method and you can’t use Substring(0, length), because it will throw ArgumentOutOfRangeException if your length is greater than the length of a string.

But! You should never ever use extension method for anything that has a potential to be mocked. I have no idea what NHibernate developers thought when they used it for method that returns the result of a query.

So, how to mock the Query method?

1. Use wrapper

Query method is from the NHibernate.Linq namespace, so if the namespace is not included, the method is not found and code is not compiled. Include your own

namespace Nerula.Linq
{
  public static class NHibernateLinqExtension {
    public static IQueryable<TEntity> Query<TEntity>(this ISession session)
    {
      return NHibernate.Linq.LinqExtensionMethods.Query<TEntity>(session);
    }
  }
}

Replacing the using NHibernate.Linq with using Nerula.Linq won’t change anything, except the app is now calling the NHibernate Query through our wrapper.

2. Call mockable interface from wrapper

Instead of just calling another static method, create an interface that is used to perform the static methods and create a default implementation of the interface that calls the original extension methods:

namespace Nerula.Linq
{
  public interface ISessionLinq
  {
    IQueryable<TEntity> Query<TEntity>(ISession session);
  }
  public static class NHibernateLinqExtension {
    internal static ISessionLinq SessionLinq {get;set;}
        
    static NHibernateLinqExtension()
    {
      SessionLinq = new NHiberanteSessionLinq();
    }

    private class NHiberanteSessionLinq : ISessionLinq
    {
      public IQueryable<TEntity> Query<TEntity>(ISession session)
      {
        return NHibernate.Linq.LinqExtensionMethods.Query<TEntity>(session);
      }
    }

    public static IQueryable<TEntity> Query<TEntity>(this ISession session)
    {
      return SessionLinq.Query<TEntity>(session);
    }
  }
}

Notice that SessionLinq has an internal access, you can configure your test projects to access to the internal properties or simply change the property to public. Now, we have a default implementation that will call the static methods for the program, but we can also change the implementation during tests and return whatever we want.

3. Mock your queries

Now, we can replace the default implementation of ISessionLinq with mocked one and finally use memory lists and other IEnumerables oodies to mock the queries.

ISession session = Mock.Of<ISession>();
Mock<ISessionLinq> sessionLinq = new Mock<ISessionLinq>(MockBehavior.Strict);

Conjecture[] conjectures = new[] 
{
  new Conjecture("Minor work", 10),
  new Conjecture("Bug fix", 50),
  new Conjecture("Simple feature", 100),
  new Conjecture("Complicated feature", 500),
};

sessionLinq.Setup(x => x.Query<Conjecture>(session))
  .Returns(conjectures.AsQueryable());
// Here is the change of the query provider
NHibernateLinqExtension.SessionLinq = sessionLinq.Object;

var largeConjectureName =
  from conjecture in session.Query<Conjecture>()
  where conjecture.Amount >= 100
  select conjecture.Name;

CollectionAssert.AreEqual(new[] { "Simple feature", "Complicated feature" }, largeConjectureName.ToArray());

4. Restore default property

Since we are changing the static property, we must make sure to change it back after the test has run, otherwise all tests would have to make sure to set correct implementation of ISessionQuery, e.g. NUnit reuses the instance of test fixture for all test and if one test is mocking the Query method while other uses NHibernate.Linq Query method, they would be order dependent. NUnit has an action attributes that make this very simple.

Conclusion

I have found that using IEnumerable to test NHibernate linq queries makes writing tests much easier and faster. You can’t use it for testing other NHibernate API used to access the database and you have to be careful about SQL vs IEnumerable semantic.

The Query extension method is a horrible design and if you are using NHibernate, you should consider rolling a facade. Not only for mocking queries, but Entity Framework is getting better and better and possible switch would be much smoother. NHibernate has recently released version 4.0, but except for support of BCL collections, I don’t find release notes very newsworthy.