Efcore: Issue when using spatialite in IIS

[auri179]

Hello,

I am using Sqlite with entityframework core and NettopologySuite (Spatialite)

I embedded everything in a web api

Everything works fine when I launch the API in debug or .NET Core, but when I deploy the API in IIS (8.5 W2012 Server R2) .

It doesn't work giving me the following message :

SqliteException: SQLite Error 1: 'Le module spécifié est introuvable.
 '.
Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(int rc, sqlite3 db)
Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
Microsoft.Data.Sqlite.SqliteCommand.ExecuteNonQuery()
Microsoft.EntityFrameworkCore.Infrastructure.SpatialiteLoader.Load(DbConnection connection)
Microsoft.EntityFrameworkCore.Sqlite.Storage.Internal.SqliteRelationalConnection.LoadSpatialite()
Microsoft.EntityFrameworkCore.Sqlite.Storage.Internal.SqliteRelationalConnection.Open(bool errorsExpected)
Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable<T>+Enumerator.BufferlessMoveNext(DbContext _, bool buffer)
Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable<T>+Enumerator.MoveNext()
Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider._TrackEntities<TOut, TIn>(IEnumerable<TOut> results, QueryContext queryContext, IList<EntityTrackingInfo> entityTrackingInfos, IList<Func<TIn, object>> entityAccessors)+MoveNext()
Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider+ExceptionInterceptor<T>+EnumeratorExceptionInterceptor.MoveNext()

I don't know what is the module and why it is not working in IIS. I've launched the API in the same server with dotnet run. Everything is fine

I am stuck..

Environment
.NET Core standard 2.2
Windows 2012 Server R2
IIS 8.5
Below the nuget packages

Any help appreciated

[auri179]

PackageReference Include="Microsoft.AspNetCore.App" /
PackageReference Include="Microsoft.AspNetCore.Razor.Design" Version="2.2.0" PrivateAssets="All" /
PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="2.2.4" /
PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="2.2.4" /
PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite.NetTopologySuite" Version="2.2.4" /
PackageReference Include="NetTopologySuite.IO.GeoJSON" Version="1.15.2" /
PackageReference Include="NetTopologySuite.IO.ShapeFile" Version="1.15.3" /
PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="2.2.0" /
PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL.NetTopologySuite" Version="2.2.0" /
PackageReference Include="TechCloud.Tools.Business" Version="1.14.0" /

[auri179]

Any ideas ?

[ajcvickers]

@auri179 There is nothing immediately obvious to me, but this looks more like an issue with the underlying layers, rather than an issue with EF itself. Have you tried using SpatiaLite module without using EF Core?

/cc @bricelam

[bricelam]

Does mod_spatialite.dll get deployed with your app? Is your app targeting .NET Framework? Could you attach a simple repro app so we can investigate further?

[auri179]

I've investigated and it appears that it might come from mod_spatialite.dll

I've created a console app everythiing is fine till I put x => x.UseNetTopologySuite() in my context OnConfiguring(DbContextOptionsBuilder optionsBuilder)

mod_spatialite.dll is present in those directories

publish\runtimes\win-x86\native
publish\runtimes\win-x64\native

Which are relevant because I am in windows environment.

My app is targeting the netstandard 2.2, I've tried to downgrade to netstandard 2.0 same error

[auri179]

Here is a repository with the consoleApp unfortunately I can't put the database which is 7 Gig.
But database is fine no need to connect through the error is coming before. I can say that if the app complains about file not found it is done !

https://github.com/auri179/SpatialiteIssue

[ajcvickers]

Triage: discuss with @bricelam

[clarkgranum]

There is a bug in SpatialiteLoader.cs::FindExtension(). The problem is when the candidate assets are built:

if (string.Equals( Path.GetFileName(file.Path), "mod_spatialite" + _sharedLibraryExtension, StringComparison.OrdinalIgnoreCase)) { var fallbacks = rids.IndexOf(group.Runtime); if (fallbacks != -1) { candidateAssets.Add(library.Path + "/" + file.Path, fallbacks); } }

library.Path is "mod_spatialite\4.3.0.1" so the candidate asset path will look like "mod_spatialite\4.3.0.1\runtimes{rid}\native" but when the .NET Core app is deployed the native dlls get flattened into a runtimes folder in the app directory called "runtimes{rid}\native." A simple solution could be to add just the file path as an additional candidate:

if (string.Equals( Path.GetFileName(file.Path), "mod_spatialite" + _sharedLibraryExtension, StringComparison.OrdinalIgnoreCase)) { var fallbacks = rids.IndexOf(group.Runtime); if (fallbacks != -1) { candidateAssets.Add(file.Path, fallbacks); candidateAssets.Add(library.Path + "/" + file.Path, fallbacks); } }

This is probably related to #17864. I am using ASP.NET Core 2.2 and EF Core 2.2.0. I am deploying an application without IIS, but I imagine it would have the same issue. I'm not sure if this is still an issue in .NET Core 3.

[bricelam]

(Re-openeing to consider patching 3.1 with this fix)

[emilbm]

I'd just like to chime in that I'm experiencing similar issues when trying to deploy to Azure Functions (v3). I'm not quite familiar with the Spatialite world to know if it's the same problem, but the message is identical (SQLite Error 1: 'The specified module could not be found.), and the stack trace is very similar:

   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteNonQuery()
   at Microsoft.Data.Sqlite.SqliteConnectionExtensions.ExecuteNonQuery(SqliteConnection connection, String commandText, SqliteParameter[] parameters)
   at Microsoft.Data.Sqlite.SqliteConnection.LoadExtensionCore(String file, String proc)
   at Microsoft.Data.Sqlite.SqliteConnection.LoadExtension(String file, String proc)
   at Microsoft.EntityFrameworkCore.Infrastructure.SpatialiteLoader.Load(DbConnection connection)
   at Microsoft.EntityFrameworkCore.Sqlite.Storage.Internal.SqliteRelationalConnection..ctor(RelationalConnectionDependencies dependencies, IRawSqlCommandBuilder rawSqlCommandBuilder)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.ServiceProviderEngine.GetService(Type serviceType, ServiceProviderEngineScope serviceProviderEngineScope)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.ServiceProviderEngineScope.GetService(Type serviceType)
   at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetService[T](IServiceProvider provider)
   at Microsoft.Extensions.DependencyInjection.SqliteServiceCollectionExtensions.<>c.<AddEntityFrameworkSqlite>b__0_0(IServiceProvider p)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.ServiceProviderEngine.GetService(Type serviceType, ServiceProviderEngineScope serviceProviderEngineScope)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.ServiceProviderEngineScope.GetService(Type serviceType)
   at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService(IServiceProvider provider, Type serviceType)
   at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService[T](IServiceProvider provider)
   at Microsoft.EntityFrameworkCore.DbContext.get_DbContextDependencies()
   at Microsoft.EntityFrameworkCore.DbContext.get_InternalServiceProvider()
   at Microsoft.EntityFrameworkCore.DbContext.Microsoft.EntityFrameworkCore.Infrastructure.IInfrastructure<System.IServiceProvider>.get_Instance()
   at Microsoft.EntityFrameworkCore.Infrastructure.Internal.InfrastructureExtensions.GetService[TService](IInfrastructure`1 accessor)
   at Microsoft.EntityFrameworkCore.Infrastructure.AccessorExtensions.GetService[TService](IInfrastructure`1 accessor)
   at Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade.get_Dependencies()
   at Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade.EnsureCreated()

I'm using 3.1.7 version of all EFCore libraries, and 2.0.3 of SQLitePCLRaw.bundle_winsqlite3.

Can you confirm or deny that this is the same problem still present?

[bricelam]

@emilbm Probably unrelated. Azure Functions perform some magic to make native libraries load, but it falls down when a native library tries to load another native library.

Follow the rabbit hole here: https://github.com/dotnet/runtime/issues/3221

[emilbm]

Alright, thanks @bricelam. Do you by any chance know of a workaround to force it to load?

[bricelam]

Make .NET load the library:

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

static void Main()
{
    // Let P/Invoke load mod_spatialite
    spatialite_version();

    using var connection = new SqliteConnection("Data Source=:memory:");
    connection.EnableExtensions();
    connection.Open();

    var command = connection.CreateCommand();
    command.CommandText = @"SELECT load_extension('mod_spatialite')";
    command.ExecuteNonQuery();

    command.CommandText = "SELECT spatialite_version()";
    var version = command.ExecuteScalar();
    Console.WriteLine(version);
}