Unit test fluent nhibernate repository with in memory sql lite - no such table error

Question

I'm trying to write unit tests for a repository pattern using Fluent NHibernate. I chose to use an in-memory sqlite database in order to avoid sql server access (which can be done for integration tests).

Here are the classes I used:

public enum ExpenseCategory
{
    Eat,
    Clothes,
    Car,
    Leisure,
    Rent,
    House,
    Lecture,
    Trip,
    Restaurent
}

public class Expense
{
    public virtual int Id { get; set; }
    public virtual ExpenseCategory Category { get; set; }
    public virtual double Amount { get; set; }
    public virtual bool IsNecessary { get; set; }
    public virtual bool IsPeriodic { get; set; }
    public virtual string Comment { get; set; }
}

    public ExpenseMapping()
    {
        Table("Expense");
        Id(x => x.Id).Column("idexpense");
        Map(x => x.Category).Column("category");
        Map(x => x.Amount).Column("amount");
        Map(x => x.IsNecessary).Column("isnecessary");
        Map(x => x.IsPeriodic).Column("isperiodic");
        Map(x => x.Comment).Column("comment");
    }
    interface IRepository<T>
{
    T GetById(int id);
    void SaveOrUpdate(T entity);
    void Delete(T entity);
    List<T> GetAll();
}

public interface IDatabase
{
    Configuration Config { get; set; }
    ISessionFactory Session { get; set; }
}

public class NhibernateRepository<T> : IRepository<T>
{
    private readonly Configuration _configuration;
    private readonly ISessionFactory _session;

    public NhibernateRepository(IDatabase database)
    {
        _configuration = database.Config;
        _session = database.Session;

    }

    public T GetById(int id)
    {
        T retrievedObject;
        using (var session = _session.OpenSession())
        {
            using (var transaction = session.BeginTransaction())
            {
                retrievedObject = session.Get<T>(id);
                transaction.Commit();
            }
        }
        return retrievedObject;
    }

    public void SaveOrUpdate(T entity)
    {
        using (var session = _session.OpenSession())
        {
            using (var transaction = session.BeginTransaction())
            {
                session.SaveOrUpdate(entity);
                transaction.Commit();
            }
        }
    }

    public void Delete(T entity)
    {
        using (var session = _session.OpenSession())
        {
            using (var transaction = session.BeginTransaction())
            {
                session.Delete(entity);
                transaction.Commit();
            }
        }
    }

    public List<T> GetAll()
    {
        IList<T> allObjects;
        using (var session = _session.OpenSession())
        {
            using (var transaction = session.BeginTransaction())
            {
                allObjects = session.CreateCriteria(typeof (T)).List<T>();
                transaction.Commit();
            }
        }

        return (List<T>)allObjects;
    }
}


public class DatabaseSqlLite : IDatabase, IDisposable
{
    public Configuration Config { get; set; }
    public ISessionFactory Session { get; set; }

    public DatabaseSqlLite()
    {
        Session = Fluently.Configure()
            .Database(SQLiteConfiguration.Standard.InMemory().ShowSql())
            .Mappings(m => m.FluentMappings
                            .Add(typeof(ExpenseMapping))
                      )
            .ExposeConfiguration(x => Config = x)
            .BuildSessionFactory();

        SchemaExport export = new SchemaExport(Config);
        export.Execute(true, true, false, Session.OpenSession().Connection, null);

    }

    public void Dispose()
    {
        //S.Dispose();
    }
}

public class DatabaseSqlServer : IDatabase
{
    public Configuration Config { get; set; }
    public ISessionFactory Session { get; set; }

    public DatabaseSqlServer()
    {
       Config = Fluently.Configure()
       .Database(MsSqlConfiguration.MsSql2008
                    .ConnectionString(m => m.Server(@".\SqlExpress")
                    .Database("databasename")
                    .TrustedConnection()))
                .Mappings(m => m.FluentMappings.AddFromAssembly(Assembly.GetExecutingAssembly()))
                .BuildConfiguration();

       Session = Config.BuildSessionFactory();
    }
}

Using DatabaseSqlServer class from a console application gives me correct results. Trying to unit test using DatabaseSqlLite class gives me errors:

public class RespositoryTest
{
    [Fact]
    public void InsertAndLoadExpense()
    {
        var database = new DatabaseSqlLite();

        var repository = new NhibernateRepository<Expense>(database);

        var expense = new Expense()
            {
                Amount = 3,
                IsNecessary = true,
                IsPeriodic = true,
                Category = ExpenseCategory.Car
            };
        repository.SaveOrUpdate(expense);
        Assert.Equal(1, repository.GetAll().Count);
    }
}

Errors:

PRAGMA foreign_keys = OFF

drop table if exists Expense

PRAGMA foreign_keys = ON

create table Expense (
    idexpense  integer primary key autoincrement,
   category TEXT,
   amount DOUBLE,
   isnecessary BOOL,
   isperiodic BOOL,
   comment TEXT
)

NHibernate: INSERT INTO Expense (category, amount, isnecessary, isperiodic, comment) VALUES (@p0, @p1, @p2, @p3, @p4); select last_insert_rowid();@p0 = 'Car' [Type: String (0)], @p1 = 3 [Type: Double (0)], @p2 = True [Type: Boolean (0)], @p3 = True [Type: Boolean (0)], @p4 = NULL [Type: String (0)]

NHibernate.Exceptions.GenericADOException could not insert: [MoneyManagerCore.Expense][SQL: INSERT INTO Expense (category, amount, isnecessary, isperiodic, comment) VALUES (?, ?, ?, ?, ?); select last_insert_rowid()] à NHibernate.Id.Insert.AbstractReturningDelegate.PerformInsert(SqlCommandInfo insertSQL, ISessionImplementor session, IBinder binder) à NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object[] fields, Boolean[] notNull, SqlCommandInfo sql, Object obj, ISessionImplementor session) à NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object[] fields, Object obj, ISessionImplementor session) à NHibernate.Action.EntityIdentityInsertAction.Execute() à NHibernate.Engine.ActionQueue.Execute(IExecutable executable) à NHibernate.Event.Default.AbstractSaveEventListener.PerformSaveOrReplicate(Object entity, EntityKey key, IEntityPersister persister, Boolean useIdentityColumn, Object anything, IEventSource source, Boolean requiresImmediateIdAccess) à NHibernate.Event.Default.AbstractSaveEventListener.PerformSave(Object entity, Object id, IEntityPersister persister, Boolean useIdentityColumn, Object anything, IEventSource source, Boolean requiresImmediateIdAccess) à NHibernate.Event.Default.AbstractSaveEventListener.SaveWithGeneratedId(Object entity, String entityName, Object anything, IEventSource source, Boolean requiresImmediateIdAccess) à NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.SaveWithGeneratedOrRequestedId(SaveOrUpdateEvent event) à NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.EntityIsTransient(SaveOrUpdateEvent event) à NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.PerformSaveOrUpdate(SaveOrUpdateEvent event) à NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.OnSaveOrUpdate(SaveOrUpdateEvent event) à NHibernate.Impl.SessionImpl.FireSaveOrUpdate(SaveOrUpdateEvent event) à NHibernate.Impl.SessionImpl.SaveOrUpdate(Object obj) à MoneyManagerRepository.NhibernateRepository`1.SaveOrUpdate(T entity) dans NhibernateRepository.cs: line 56 à UnitTests.RespositoryTest.InsertAndLoadExpense() dans RespositoryTest.cs: line 28 System.Data.SQLite.SQLiteException SQL logic error or missing database no such table: Expense

Can anyone help me to resolve this?

Should I keep the "using syntax" when opening the session or should I open the session only at the begining ? I tried both but still having the same error. — david hale

Firo Firo · Accepted Answer · 2013-06-06T12:06:50

What happens is that you open a connection, create the tables and throw away the connections, then you open another connection which points to an empty database.

Using sqlite inmemory the connection must be the same all the time because the inmemory database lives and dies with the connection.

i would:

make public ISessionFactory Session { get; set; } private and rename it to sessionfactory since Session is misleading
have a Method public ISession OpenSession() and in SqlLiteDatabase implement it return _sessionfactory.OpenSession(_connection); where connection is the same connection used in SchemaExport().Execute

Unit test fluent nhibernate repository with in memory sql lite - no such table error

1 Answers