Can't use the SQLite20Driver on windows arm64

[bounav]

Hello, we're trying to use SQLite20Driver with NHibernate to talk to... well SQLite.

It works fine an x64 machine, but gives the following error at runtime on Windows arm64:

----> System.Reflection.TargetInvocationException : Exception has been thrown by the target of an invocation.
----> System.DllNotFoundException : Unable to load DLL 'SQLite.Interop.dll' or one of its dependencies: The specified module could not be found. (0x8007007E)
at FluentNHibernate.Cfg.FluentConfiguration.BuildSessionFactory()

NHibernate depends on System.Data.Sqlite which only supports x86 and x64.

From what I could find there is another nuget package called Microsoft.Data.Sqlite that might be an easy drop in replacement and hopefully work on arm64 machines too?

The Microsoft documentation compares System.Data.SQLite to Microsoft.Data.SQLite...

Is anybody else stuck trying to talk to SQLite on an arm64 machine?

[bounav]

As proof of concept, we got it to work on both our x64 and arm64 machines by doing the following:

We created a different dialect for SQLite because the built-in SQLiteDialect was throwing a The requested collection 'DataTypes' is not defined ArgumentException at runtime when bootstrapping NHibernate:

    public class SqliteDataBaseMetaData(
        DbConnection connection,
        NHibernate.Dialect.Dialect dialect
    ) : NHibernate.Dialect.Schema.SQLiteDataBaseMetaData(connection, dialect)
    {
        public SqliteDataBaseMetaData(
            DbConnection connection
        ) : this(connection, new SqliteDialect()) { }

        // Without this a "System.ArgumentException : The requested collection 'DataTypes' is not defined." is thrown
        public override bool IncludeDataTypesInReservedWords => false;
    }

    public class SqliteDialect : NHibernate.Dialect.SQLiteDialect
    {
        public override NHibernate.Dialect.Schema.IDataBaseSchema GetDataBaseSchema(DbConnection connection)
        {
            var schema = new SqliteDataBaseMetaData(connection, this);
            return schema;
        }
    }

Then, based on how SQLite20Driver is implemented, we implemented another driver that load the .DLLs from Microsoft.Data.Sqlite.

This library targets .net standard 2.0 and work on both x64 and arm64 devices.

    /// <summary>
    /// NHibernate driver for the Microsoft.Data.Sqlite data provider for .NET.
    /// </summary>
    /// <remarks>
    /// <para>
    /// In order to use this driver you must have the Microsoft.Data.Sqlite.dll assembly available
    /// for NHibernate to load. This assembly includes the SqLite.dll or SQLite3.dll libraries.
    /// </para>
    /// <para>
    /// You can get the Microsoft.Data.Sqlite.dll assembly from
    /// <a href="https://www.nuget.org/packages/Microsoft.Data.Sqlite">https://www.nuget.org/packages/Microsoft.Data.Sqlite</a>
    /// </para>
    /// <para>
    /// Please check <a href="https://learn.microsoft.com/en-gb/dotnet/standard/data/sqlite/">https://learn.microsoft.com/en-gb/dotnet/standard/data/sqlite/</a> for more information regarding SqLite.
    /// </para>
    /// </remarks>
    public class MicrosoftSqLiteDriver : ReflectionBasedDriver
    {
        /// <summary>
        /// Initializes a new instance of <see cref="SQLite20Driver"/>.
        /// </summary>
        /// <exception cref="HibernateException">
        /// Thrown when the <c>SQLite.NET</c> assembly can not be loaded.
        /// </exception>
        public MicrosoftSqLiteDriver() : base(
            "Microsoft.Data.Sqlite.Core",
            "Microsoft.Data.Sqlite",
            "Microsoft.Data.Sqlite.SqliteConnection",
            "Microsoft.Data.Sqlite.SqliteCommand")
        {
        }

        public override IResultSetsCommand GetResultSetsCommand(ISessionImplementor session)
        {
            return new BasicResultSetsCommand(session);
        }

        public override bool UseNamedPrefixInSql => true;

        public override bool UseNamedPrefixInParameter => true;

        public override string NamedPrefix => "@";

        public override bool SupportsMultipleOpenReaders => false;

        public override bool SupportsMultipleQueries => true;

        public override bool SupportsNullEnlistment => false;

        public override bool HasDelayedDistributedTransactionCompletion => true;
    }

Finally, we initialise NHibernate with something like this:

var filename = "example.sqlite";

var connectionStringBuilder = new SqliteConnectionStringBuilder
                             {
                                 DataSource = filename,
                                 ForeignKeys = true,
                                 Mode = SqliteOpenMode.ReadWriteCreate,
                                 Cache = SqliteCacheMode.Shared,
                                 DefaultTimeout = 10 // Defaults to 30
                             };

var sqliteConnectionString = connectionStringBuilder.ToString();

var config = SQLiteConfiguration.Standard
                                .ConnectionString(sqliteConnectionString)
                                .AdoNetBatchSize(0)
                                .Driver<MicrosoftSqLiteDriver>()
                                .Dialect<SQLiteDialect>();

var sessionFactory = Fluently.Configure()
                         .Database(config)
                         .Mappings(m => { /* omitted for brevity */ })
                         .ExposeConfiguration(cfg => { /* omitted for brevity */ })
                         .Mappings(m => m.FluentMappings.Conventions.Add(new UtcConvention()))
                         .BuildSessionFactory();

Hope this helps!

[hazzik]

As Frédéric answered, writing own driver (or using beginor's version) is the way to go. Why? For us, Microsoft's implementation of Sqlite has too many limitations, and providing work arounds and support them is beyond our time capacity.

As for System.Data.SQLite - you should probably submit a feature request for arm64 to the maintainers of that project.

[bounav]

Thanks. That makes sense.

[fredericDelaporte]

Writing your custom driver (and dialect if need be) is a way of using the Microsoft provider.

Some third party extensions also provide this, like https://github.com/beginor/nhibernate-extensions.