nHibernate is one of the most popular and mature tool for object relational mapping. It does a great job speeding up development, offering a lot of flexibility in configuration and simply making work with database much more pleasent – because of this it’s so commonly used. One of its key features is first and second level caching, which helps to cut out excessive database workload by avoiding unnecessary calls to it. However, sometimes generally helpful cache mechanism leads to unexpected behaviour. A month ago we faced a weird issue – our sql queries results didn’t met criteria specified in where statements.

For the sake of simplicity let’s assume we have a C# class Employee containing properties for Name and LayoffDate, and corresponding database table Employees. Now it’s time for a method which fetches data from the database with a parameter telling wether we want all employees ever working in company or only currently employed ones:

*/

private IList<Employee> GetEmployees(bool withLayedOffOnes)
{
    return Query<Employee>()
        .Where(employee => withLayedOffOnes
        ? employee.LayoffDate.HasValue || employee.LayoffDate == null
        : employee.LayoffDate == null)
        .ToList();
}

/*

Quite straightforward. Let’s make sure the method works and executing it with different value of the parameter produces expected output. Time to write a few tests.

For explanation: CreateEmployee method is used to write passed Employee Name and LayoffDate to the database, but I’ll skip the implementation as it is not important in our example.

*/

[Test]
public void GetEmployees_Should_Return_Only_Current_Employees_When_withLayedOffOnes_Is_Set_To_False()
{
    var employee = CreateEmployee("Employee 1", null);
    var laidOffEmployee = CreateEmployee("Laid off employee 1", DateTime.Now);

    var currentEmployees = GetEmployees(withLayedOffOnes: false);

    Assert.That(currentEmployees.Count, Is.EqualTo(1));

    Assert.That(currentEmployees.Contains(employee));
}

[Test]
public void GetEmployees_Should_Return_All_Employees_When_withLayedOffOnes_Is_Set_To_True()
{
    var employee = CreateEmployee("Employee 1", null);
    var laidOffEmployee = CreateEmployee("Laid off employee 1", DateTime.Now);

    var allEmployees = GetEmployees(withLayedOffOnes: true);

    Assert.That(allEmployees.Count, Is.EqualTo(2));

    Assert.That(allEmployees.Contains(employee));    
    Assert.That(allEmployees.Contains(laidOffEmployee));
}

/*

Tests passed, everything is ok. Now it’s time for a case with query caching. Let’s make sure that feeding the method with different value of withLayedOffOnes parameter and comparing its results produces a different collection.

*/

[Test]
public void GetEmployees_Should_Return_All_Employees_When_withLayedOffOnes_Is_Set_To_True()
{
    var employee = CreateEmployee("Employee 1", null);    
    var laidOffEmployee = CreateEmployee("Laid off employee 1", DateTime.Now);

    var currentEmployees = GetEmployees(withLayedOffOnes: false);
    var allEmployees = GetEmployees(withLayedOffOnes: true);

    CollectionAssert.AreNotEquivalent(currentEmployees, allEmployees);
}

/*

Test fails, but how is it possible? The reason behind it is nHibernate compiles linq queries to HQL (Hibernate Query Language) on first execution and uses the compiled result from now on to save time on translating the LINQ every time. The conditional expression in Where() method was evaluated before the compilation happened. This way parameter passed for the first time will determine every following execution of the GetEmployees method, no matter what value will be passed to it.

Described problem is hard to notice at first glance, so double check your queries gets compiled correctly. Make sure to avoid ternary operator in nHibernate.Linq. Use Expression<Func<T, bool>>, custom predicate builder or simply check the condition before the query gets called instead.