Kash Farooq's software development blog

.NET Developer

Unit Testing with SQL Lite and Fluent NHibernate

Posted by Kash Farooq on January 2, 2011

I used to unit test NHibernate criteria by, essentially, interaction testing.

It’s fairly straight forward to do this (NHibernate provides lots of interfaces that are easy to mock), but the test code ends up just mirroring the production code. Your test would check that CreateCriteria was called, then an Expression was added, then List was called, etc, etc.

If the production code changes (for example, a more efficient criteria is implemented, or HQL is used), the test would need to be completely rewritten. This defeats one of the objectives of TDD – provide an environment in which refactoring can be done safely, with the confidence that the new code does exactly what the old code did.

So, I switched to using SQL Lite to allow my unit tests to actually hit an in-memory database. I think this is perfectly acceptable for simple queries that are not using any DB specific features. The tests are also fast enough to run on a Continuous Build server.

The code in this post uses Fluent NHibernate to configure NHibernate. It allows me to easily configure my production code to use a SQL Lite database via my test code, and to use a production database via my production code.

Let’s start with a very simple Model – the data to be persisted.

namespace SystemUnderTest.Model {
  public class Person {
    public virtual long Id { get; set; }
    public virtual string FirstName { get; set; }
    public virtual string LastName { get; set; }
    public virtual DateTime DateOfBirth { get; set; }

Next, we need some code that configures NHibernate using Fluent, and allows me to set the database type to use. Note that the code exposes the Session Factory so I can create a session when needed. It also exposes the NHibernate configuration. I will use this from the unit test to create a schema inside my SQL Lite in-memory database.

namespace SystemUnderTest.DataAccess {
  public class DataSession {
    private readonly IPersistenceConfigurer _dbType;
    public DataSession(IPersistenceConfigurer dbType) {
      _dbType = dbType;

    private ISessionFactory _sessionFactory;
    private Configuration _configuration;

    public ISessionFactory SessionFactory {
      get { return _sessionFactory; }

    public Configuration Configuration {
      get { return _configuration; }

    private void CreateSessionFactory() {
      _sessionFactory = Fluently
      //Only map classes in the Assembly of 'Person' that have a namespace ending in .Model
      .Mappings(m => m.AutoMappings.Add(
         AutoMap.AssemblyOf<Person>().Where(type => type.Namespace.EndsWith(".Model"))))
      .ExposeConfiguration(cfg => _configuration = cfg)

Now, let’s look at a unit test. I will be creating a PersonRepository to save objects, query objects, etc. The test setup for this class needs to tell the production code ‘DataSession’ class to use an in-memory SQL Lite instance, and to build a schema inside this instance based on the configuration created by Fluent NHibernate.

public class PersonRepositoryTest {
 private DataSession _dataSession;
 private ISession _session;

 public void TestFixtureSetUp() {
    //Use In Memory database, open session and Build Schema inside In Memory database
    _dataSession = new DataSession(SQLiteConfiguration.Standard.InMemory());
    _session = _dataSession.SessionFactory.OpenSession();
    BuildSchema(_session, _dataSession.Configuration);

 public void BuildSchema(ISession session, Configuration configuration) {
   var export = new SchemaExport(configuration);
   export.Execute(true, true, false, session.Connection, null);

 public void TestFixtureTearDown() {
   //Clean up after tests have run

 public void SetUp() {
   CleanTheTables(); //Make sure tables are empty before each test runs

Now, a simple test to begin with. Create a person, save it using our PersonRepository class and then make sure we can find it inside the in-memory database.

public void SaveAPersonTest() {
  var personRepository = new PersonRepository(_session);
     new Person{DateOfBirth = new DateTime(1970,1,1),FirstName = "John",LastName = "Smith"});

  //Now get all Person objects from DB and make sure our object has been persisted
  IList<Person> people = _session.CreateCriteria<Person>().List<Person>();


Now for something a little more complicated; something that we could implement using several methods (e.g. using Criteria, HQL, SQL).

public void FindPeopleOlderThanCertainAgeTest() {
  var personRepository = new PersonRepository(_session);

  //Use repository to find people older than 30
  IList<Person> peopleOlderThan30 = personRepository.GetAllPeopleOlderThan(30);

This test provides our TDD safeguard. We can implement GetAllPeopleOlderThan any way we like and this test will ensure that it has been implemented correctly. We can refactor the production code at a later date and the test will ensure we haven’t introduced a bug.

For completeness, here is my implemented repository class:

public class PersonRepository {
  private readonly ISession _session;
  public PersonRepository(ISession session) {
    _session = session;

  public void Save(Person person) {

  public IList<Person> GetAllPeopleOlderThan(int ageInYears) {
    ICriteria criteria = _session.CreateCriteria<Person>();
    DateTime datePersonMustBeBornBefore = DateTime.Now.AddYears(-1*ageInYears);
    criteria.Add(Expression.Le("DateOfBirth", datePersonMustBeBornBefore));
    return criteria.List<Person>();

One Response to “Unit Testing with SQL Lite and Fluent NHibernate”

  1. I like your idea of table cleaning before each test, it isolates them very well. Unfortunately it is not possible to take advantage of parallel execution.

Sorry, the comment form is closed at this time.

%d bloggers like this: