efcore: In EF7, SqLite unable to load spatialite using the NetTopologySuite Package

Hi,

In EF 7 I using the Microsoft.EntityFrameworkCore.Sqlite.NetTopologySuite package a simple example does not seem to be able to load the spatialite library

I get “Microsoft.Data.Sqlite.SqliteException: ‘SQLite Error 1: ‘’.’” implying sqlite has returned an error code, but not given any more information about it.

This was all working fine in EF 6

Stacktrace:-

 	 	Microsoft.Data.Sqlite.dll!Microsoft.Data.Sqlite.SqliteConnection.LoadExtensionCore(string file, string proc)	Unknown
 	Microsoft.Data.Sqlite.dll!Microsoft.Data.Sqlite.SqliteConnection.LoadExtension(string file, string proc)	Unknown
 	Microsoft.EntityFrameworkCore.Sqlite.dll!Microsoft.EntityFrameworkCore.Infrastructure.SpatialiteLoader.Load(System.Data.Common.DbConnection connection)	Unknown
 	Microsoft.EntityFrameworkCore.Sqlite.dll!Microsoft.EntityFrameworkCore.Sqlite.Storage.Internal.SqliteRelationalConnection.InitializeDbConnection(System.Data.Common.DbConnection connection)	Unknown
 	Microsoft.EntityFrameworkCore.Sqlite.dll!Microsoft.EntityFrameworkCore.Sqlite.Storage.Internal.SqliteRelationalConnection.SqliteRelationalConnection(Microsoft.EntityFrameworkCore.Storage.RelationalConnectionDependencies dependencies, Microsoft.EntityFrameworkCore.Storage.IRawSqlCommandBuilder rawSqlCommandBuilder, Microsoft.EntityFrameworkCore.Diagnostics.IDiagnosticsLogger<Microsoft.EntityFrameworkCore.DbLoggerCategory.Infrastructure> logger)	Unknown

Example code:-



var sqliteConnection = new SqliteConnection($"DataSource=c:\\temp\\testdb.db");
var _connection = sqliteConnection;
_connection.Open();

var optionsBuilder = new DbContextOptionsBuilder<DbContext>();
optionsBuilder.UseSqlite(_connection, arg => arg.UseNetTopologySuite());

About this issue

Original URL
State: closed
Created 2 years ago
Reactions: 5
Comments: 18 (7 by maintainers)

Commits related to this issue

Update to SQLitePCLRaw 2.1.4 Fixes #29584 — committed to bricelam/efcore by bricelam a year ago
[release/7.0] Update to SQLitePCLRaw 2.1.4 (#30036) Fixes https://github.com/dotnet/efcore/issues/29584 — committed to dotnet/efcore by bricelam a year ago

Most upvoted comments

I faced the same issue and came up with the following work-a-round, which is working for me (.net 7 / EF Core 7 / Windows):

In my database context in the OnConfiguring call:

protected override void OnConfiguring(DbContextOptionsBuilder options)
    {
      base.OnConfiguring(options);

      options.UseSqlite($"Data Source={m_SQLiteDBPath}",
      o =>
      {
        o.UseNetTopologySuite();
        var infrastructure = (IDbContextOptionsBuilderInfrastructure)options;
#pragma warning disable EF1001
        var sqliteExtension = options.Options.FindExtension<SqliteOptionsExtension>() ?? new SqliteOptionsExtension();

        //We need to disable LoadSpatialite but it's not provided as an option externally we need to dig into the internals...
        infrastructure.AddOrUpdateExtension(sqliteExtension.WithLoadSpatialite(false));
#pragma warning restore EF1001
      });
    }

I use a IDbContextFactory to create / load a new database context. I have p/invoke definition inside the class:

[DllImport("mod_spatialite", CallingConvention = CallingConvention.Cdecl)]
static extern IntPtr spatialite_version();

In the the CreateDbContext() call I have:

          //Prime the spationlite module, trigger a 'useless' call to trigger the load of the module in mem.
          spatialite_version();
          var connection = m_DatabaseContext.Database.GetDbConnection();
          if (connection is SqliteConnection sqliteConnection)
          {
            //We trigger an open and keep the connection open so we load the module
            sqliteConnection.Open();
            //We need to enable extensions otherwise we receive an unathorized exception
            sqliteConnection.EnableExtensions(true);
            var sqliteCommand = new SqliteCommand(@"SELECT load_extension('mod_spatialite');", sqliteConnection);
            sqliteCommand.ExecuteReader(); //We don't read anything..we don't care but it's a Select so we need to perform a Reader.
          }

Probably this can be simplified, but I noticed the p/invoke call to spatialite_version triggers the load of the module in mem, and the dynamic load of the mod_spatialite module did the trick (see https://www.gaia-gis.it/fossil/libspatialite/wiki?name=mod_spatialite).

EF Core internally use the sqlite function call to load an extension that seem to fail without any (reasonable) reason.

btw; same PackageReferences as @woodmeister

stefanlenselink on Nov 18, 2022

Thank you for the good work!

Op ma 9 jan. 2023 19:56 schreef Brice Lambson @.***>:

SQLitePCLRaw plans to release sometime this week. After which, we’ll update 1 our dependency hopefully in time for the Feburay 7.0.x patch.

1 With the caveat that the final decision about whether to patch an issue is made by the .NET Directors, not us.

— Reply to this email directly, view it on GitHub https://github.com/dotnet/efcore/issues/29584#issuecomment-1376130759, or unsubscribe https://github.com/notifications/unsubscribe-auth/AL4NXWMVVK5F4VY246LBWILWRRNOTANCNFSM6AAAAAASCDCG7E . You are receiving this because you commented.Message ID: @.***>

BartjeD on Jan 9, 2023

Workaround good for me too.

I needed to put the sqlitecommand in a using so it get’s disposed, otherwise there are problems with the machinery later on.

woodmeister on Nov 21, 2022