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

[woodmeister]

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());

[ajcvickers]

See https://learn.microsoft.com/en-us/ef/core/providers/sqlite/spatial#installing-spatialite

[woodmeister]

This on windows, so it should be pulled in by nuget? The spatialite binaries appear to be present in the runtimes folder as they were in the old version.

[woodmeister]

Further update,

This is my project file:-

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net7.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>

  <ItemGroup>
	  <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="7.0.0" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite.NetTopologySuite" Version="7.0.0" />
  </ItemGroup>

</Project>

If I change to net6.0 and 6.0.0 for the packages then it works, with net7.0 I get the error above.

[stefanlenselink]

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

[woodmeister]

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.

[BartjeD]

We also encountered this issue with the topology suite and SQLITE in EF Core 7.

We altered the above work around to use:

[LibraryImport("mod_spatialite")]
[UnmanagedCallConv(CallConvs = new Type[] { typeof(System.Runtime.CompilerServices.CallConvCdecl) })]
private static partial IntPtr spatialite_version();

Which I can't take credit for - it's a suggestion by Visual Studio.

[hallqvist]

Is there any plans to get this fixed? Thanks :-)

[ajcvickers]

@hallqvist It is being investigated.

[hallqvist]

Sound great, thanks @ajcvickers :-)

[scott-mcdonald]

Encountered the same issue when upgrading from EF 6.0.5 to EF 7.0.1

Will have to revert back to EF 6.X for now...

Error can be reproduced easily with the following console app program:

csproj file

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net6.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>

  <ItemGroup>
    <!-- <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="6.0.12" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite.NetTopologySuite" Version="6.0.12" /> -->
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="7.0.1" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite.NetTopologySuite" Version="7.0.1" />
  </ItemGroup>

</Project>

Program.cs

using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore.Infrastructure;

namespace Test;

internal static class Program
{
    static void Main(string[] _)
    {
        try
        {
            using var connection = new SqliteConnection("Data Source=:memory:");

            connection.Open();
            connection.EnableExtensions();
            SpatialiteLoader.Load(connection);

            var command = connection.CreateCommand();
            command.CommandText = "SELECT spatialite_version()";

            var version = command.ExecuteScalar();

            System.Console.WriteLine("SpatiaLite version: " + version);
        }
        catch (Exception ex)
        {
            System.Console.WriteLine(ex);
        }
    }
}

The above code works fine with EFCore 6.X but throws an exception in EFCore 7.X

[bricelam]

This looks like an issue somewhere in SQLitePCLRaw. Filed ericsink/SQLitePCL.raw#532 to follow up.

[bricelam]

🩹 Workaround

You can work around this issue by upgrading to the latest (currently pre-release) package of SQLitePCLRaw:

  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="7.0.1" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite.NetTopologySuite" Version="7.0.1" />
+   <PackageReference Include="SQLitePCLRaw.bundle_e_sqlite3" Version="2.1.4-pre20230105221937" />
  </ItemGroup>

[scott-mcdonald]

@bricelam I have verified this work around does indeed work via unit tests. Was able to upgrade to the latest version of Entity Framework Core on my project with Sqlite with the spatial extension - thank you... Any idea when this work around will no longer be needed?

[bricelam]

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

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

[BartjeD]

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 <#29584 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AL4NXWMVVK5F4VY246LBWILWRRNOTANCNFSM6AAAAAASCDCG7E> . You are receiving this because you commented.Message ID: ***@***.***>

[capnmidnight]

If anyone reading this also lives with mild dyslexia:

The documentation at https://learn.microsoft.com/en-us/ef/core/providers/sqlite/spatial#installing-spatialite says to install package SQLitePCLRaw.bundle_sqlite3. But it should be SQLitePCLRaw.bundle_e_sqlite3.

@ajvickers already said to install that package, but I had found the learn.microsoft.com docs first, couldn't figure out the problem, saw this issue, and couldn't see what was different, because I just didn't see the little _e_ in the middle of the package name.

Whoever made those packages needs to learn something about one of the four hard computer science problems: naming things. The others being cache invalidation and off-by-one errors.

[bricelam]

@capnmidnight That code listing in the docs is for how to work around an issue with PROJ6 on macOS/Linux where you do need to use bundle_sqlite3 instead of bundle_e_sqlite3.