NHibernate tests with SQLite - no such table error when using async methods-CodePudding

I'm using NHibernate in my ASP.NET 6 app. For the purpose of integration tests, I'm using SQLite in-memory database.

This is how NHibernate configuration for integration tests looks like:

        _configuration = new Configuration();
        
        _configuration.DataBaseIntegration(db =>
        {
            db.Driver<SQLite20Driver>();
            db.Dialect<MySqliteDialect>();
            db.ConnectionProvider<SQLiteInMemoryConnectionProvider>();
            db.ConnectionString = "Data Source=:memory:;Version=3;New=True;DateTimeKind=Utc;DateTimeFormatString=yyyy-MM-dd HH:mm:ss.FFFFFFF";
            db.LogSqlInConsole = true;
            db.ConnectionReleaseMode = ConnectionReleaseMode.OnClose;
            db.HqlToSqlSubstitutions = "true=1;false=0";
            db.SchemaAction = SchemaAutoAction.Validate;
        });
        
        var mapping = new ModelMapper();
        mapping.AddMappings(typeof(ApplicationUserMapping).Assembly.GetTypes());
        // other mappings..
        var mappingDocument = mapping.CompileMappingForAllExplicitlyAddedEntities();
        _configuration.AddMapping(mappingDocument);
        
        _configuration.LinqToHqlGeneratorsRegistry<DefaultLinqToHqlGeneratorsRegistry>();
        
        var exp = new SchemaExport(_configuration);
        exp.Execute(true, true, false);
        _sessionFactory = _configuration.BuildSessionFactory();

I have SettingsService class which has the following method:

    public async Task<IList<Setting>> GetAll()
    {
        using var session = _factory.OpenSession();
        var settings = await session.QueryOver<Setting>().ListAsync();
        return settings;
    }

Now, when I call this method from a simple NUnit test:

    [Test]
    public async Task GetAll()
    {
        var settings = await new SettingsService(_sessionFactory).GetAll();
    }

I'm getting an error:

NHibernate.Exceptions.GenericADOException : could not execute query
[ SELECT this_.Id as id1_0_0_, this_.Name as name2_0_0_, this_.Value as value3_0_0_ FROM Settings this_ ]
[SQL: SELECT this_.Id as id1_0_0_, this_.Name as name2_0_0_, this_.Value as value3_0_0_ FROM Settings this_]
  ----> System.Data.SQLite.SQLiteException : SQL logic error
no such table: Settings

The whole test's output looks as follows:

    PRAGMA foreign_keys = OFF

    drop table if exists Settings

    // other tables drops...

    PRAGMA foreign_keys = ON

    create table Settings (
        Id BLOB not null,
       Name TEXT not null unique,
       Value TEXT not null,
       primary key (Id)
    )

    // other tables creation... 

NHibernate: SELECT this_.Id as id1_0_0_, this_.Name as name2_0_0_, this_.Value as value3_0_0_ FROM Settings this_

NHibernate.Exceptions.GenericADOException : could not execute query
[ SELECT this_.Id as id1_0_0_, this_.Name as name2_0_0_, this_.Value as value3_0_0_ FROM Settings this_ ]
[SQL: SELECT this_.Id as id1_0_0_, this_.Name as name2_0_0_, this_.Value as value3_0_0_ FROM Settings this_]
  ----> System.Data.SQLite.SQLiteException : SQL logic error
no such table: Settings
Data:
  actual-sql-query: SELECT this_.Id as id1_0_0_, this_.Name as name2_0_0_, this_.Value as value3_0_0_ FROM Settings this_
   at NHibernate.Loader.Loader.DoListAsync(ISessionImplementor session, QueryParameters queryParameters, IResultTransformer forcedResultTransformer, QueryCacheResultBuilder queryCacheResultBuilder, CancellationToken cancellationToken)
   at NHibernate.Loader.Loader.ListIgnoreQueryCacheAsync(ISessionImplementor session, QueryParameters queryParameters, CancellationToken cancellationToken)
   at NHibernate.Loader.Criteria.CriteriaLoaderExtensions.LoadAllToListAsync[T](IList`1 loaders, ISessionImplementor session, CancellationToken cancellationToken)
   at NHibernate.Impl.SessionImpl.ListAsync[T](CriteriaImpl criteria, CancellationToken cancellationToken)
   at NHibernate.Impl.SessionImpl.ListAsync[T](CriteriaImpl criteria, CancellationToken cancellationToken)

So you can see that the Settings table is created.

If I change the GetAll() method's implementation to not be async i.e. not use ListAsync(), but List() function:

    public IList<Setting> GetAll()
    {
        using var session = _factory.OpenSession();
        var settings = session.QueryOver<Setting>().List();
        return settings;
    }

The test passes (after removing async, Task and await from it, of course).

I've seen this question, but in my case the only difference is using async vs non-async methods of NHibernate. I use the same ISessionFactory in the integration tests' initialization code and inside the SettingsService.

Any idea what's happening here?

CodePudding user response：

According to SQLite docs:

In-Memory database ceases to exist as soon as the database connection is closed. Every :memory: database is distinct from every other. So, opening two database connections each with the filename ":memory:" will create two independent in-memory databases.

By default each time session is opened - new connection is created. So this error is expected behavior with default settings.

But you use some custom connection provider SQLiteInMemoryConnectionProvider that reuses once opened connection. So I would say the problem is inside SQLiteInMemoryConnectionProvider - it's not ready for async code.

Make sure that your connection provider implements both GetConnection and GetConnectionAsync methods. Something like:

public override DbConnection GetConnection()
{
    return  _connection ??= base.GetConnection();
}

public override async Task<DbConnection> GetConnectionAsync(CancellationToken cancellationToken)
{
    return  _connection ??= await base.GetConnectionAsync(cancellationToken);
}

Or maybe it uses ThreadStatic field for storing connection, it should use AsyncLocal instead:

private readonly AsyncLocal<DbConnection> _connection = new AsyncLocal<DbConnection>();