Why NHibernate simple select in HQL to SQLite database is not working ?

Question

I've recently started using FluenNHibernate and some weird problem appeared when I tried to write unit test with SQLite.

I use SQLite in memory database for test, for each test method I am clearing data existing in database. In example:

var u = new User() { Name = "Piotr" };
_session.Save(u);
_session.Clear();
var list = _session.CreateCriteria<User>().List();

This code works fine but when I write in the next line:

var list2 = _session.CreateQuery("FROM User").List();

I get:

System.Data.SQLite.SQLiteException: SQLite error
no such table: users

The sql query genereated by NHibernate is fine, so what can be the problem ?

Answer 1

Name pluralizaion maybe? User - users.

Answer 2

You mentioned that you're using an in-memory SQLite database. NHibernate might be deciding to close the database connection in between the two statements, which would cause you to lose all of your tables.

One way of preventing this is to create a custom IConnectionProvider implementation that allows you to explicitly control when the connection is closed.

Here's what I used to handle this in a project:

public class InMemoryConnectionProvider : IConnectionProvider
{
 private static readonly object syncObject = new object();
 private static SQLiteConnection connection;

 #region IConnectionProvider Members

 public void Configure(IDictionary<string, string> settings)
 {
 }

 public void CloseConnection(IDbConnection conn)
 {
 }

 public IDbConnection GetConnection()
 {
  CreateConnection();
  if (connection.State != ConnectionState.Open)
  {
   connection.Open();
  }
  return connection;
 }

 public IDriver Driver
 {
  get { return new SQLite20Driver(); }
 }

 public void Dispose()
 {
 }

 #endregion

 public static void CreateConnection()
 {
  lock (syncObject)
  {
   if (connection == null)
   {
    var builder = new SQLiteConnectionStringBuilder
                   {
                    DataSource = ":memory:",
                    BinaryGUID = true,
                    DateTimeFormat = SQLiteDateFormats.ISO8601
                   };
    connection = new SQLiteConnection(builder.ConnectionString);
    connection.Open();
   }
  }
 }

 public static void DestroyConnection()
 {
  lock (syncObject)
  {
   if (connection != null)
   {
    connection.Dispose();
    connection = null;
   }
  }
 }
}

You'd also need to set the connection.provider option in your config file to point to this class.

In your tests, you'd call the static InMemoryConnectionProvider.CreateConnection() method either at the beginning of the test or in a setup method. When you're done, you'd call InMemoryConnectionProvider.DestroyConnection() to close the connection. Since the implementation of IConnectionProvider.CloseConnection() is a no-op, NHibernate won't be able to close the connection on its own.

Answer 3

I've had issues with SQLite using the in-memory database. The database is destroyed when the connection/session is closed. Using the file variant seems to work better.

Also, the database entities have to be created as part of the session factory setup. Try something like in the session factory setup:

        // Create schema.
        new SchemaExport(config).Create(false, true);