Fluent nhibernate BuildSessionFactory with Microsoft.Data.Sqlite is not Working

Question

Trying to establish session factory on Microsoft.Data.Sqlite with fluent nhibernate. All references are added and are available in the bin folder on runtime.

var sessionFactory = Fluently.Configure()
    .Database(MsSqliteConfiguration.Standard.UsingFile(DbFilePath))
    .Mappings(m => m.FluentMappings.AddFromAssemblyOf<CheckExe>())
    .ExposeConfiguration(BuildSchema)
    .BuildSessionFactory();

This method throws exception as "The requested collection 'DataTypes' is not defined."

Tried adding the driver and dialect in the configuration NHibernate.Driver.MicrosoftDataSqlClientDriver and NHibernate.Dialect.SQLiteDialect both through config xml and through c# code set property . But, facing another blocker error as follows.

var cfg = new Configuration();
cfg.SetProperty("connection.connection_string", ConnectionString);
cfg.SetProperty("dialect", typeof(global::NHibernate.Dialect.SQLiteDialect).AssemblyQualifiedName);
cfg.SetProperty("connection.driver_class", typeof(global::NHibernate.Driver.MicrosoftDataSqlClientDriver).AssemblyQualifiedName);

Fluently.Configure(cfg).Mappings(m => m.FluentMappings.AddFromAssemblyOf<CheckExe>()).BuildSessionFactory();

Could not create the driver from NHibernate.Driver.MicrosoftDataSqlClientDriver, NHibernate, Version=5.4.0.0, Culture=neutral, PublicKeyToken=aa95f207798dfdb4.

Tried this way too - resulted in same error

return Fluently.Configure()
    .Database(MsSqliteConfiguration.Standard.UsingFile(DbFilePath).Driver<MicrosoftDataSqlClientDriver>().Dialect<SQLiteDialect>())
    .Mappings(m => m.FluentMappings.AddFromAssemblyOf<CheckExe>())
    .ExposeConfiguration(BuildSchema)
    .BuildSessionFactory();

Answer 1

_sessionFactory = Fluently.Configure().Database(MsSqliteConfiguration.Standard.ConnectionString(c => c.Is($"data source={_dbFilePath};")).Dialect<NHibernate.Extensions.Sqlite.SqliteDialect>().Driver<NHibernate.Extensions.Sqlite.SqliteDriver>())
.Mappings(m => m.FluentMappings.AddFromAssemblyOf())
.ExposeConfiguration(BuildSchema).BuildSessionFactory();

This worked for me and the exception got resolved; however if we try to create the session factory again then another exception occurs:

ERROR NHibernate.Tool.hbm2ddl.SchemaUpdate - could not complete schema update System.ArgumentException: More restrictions were provided than the collection 'Tables' supports. at Microsoft.Data.Sqlite.SqliteConnection.GetSchema(String collectionName, String[] restrictionValues) at NHibernate.Dialect.Schema.AbstractDataBaseSchema.GetTables(String catalog, String schemaPattern, String tableNamePattern, String[] types) at NHibernate.Dialect.Schema.SQLiteDataBaseMetaData.GetTables(String catalog, String schemaPattern, String tableNamePattern, String[] types) at NHibernate.Tool.hbm2ddl.DatabaseMetadata.GetTableMetadata(String name, String schema, String catalog, Boolean isQuoted) at NHibernate.Cfg.Configuration.GenerateSchemaUpdateScript(Dialect dialect, IDatabaseMetadata databaseMetadata) at NHibernate.Tool.hbm2ddl.SchemaUpdate.Execute(Action`1 scriptAction, Boolean doUpdate)

With or without any schema update - this occurs whenever a schema update is called.

Also, the same works fine with System.Data.Sqlite, with Microsoft.Data.Sqlite the exception occurs.