Document that Microsoft.Data.Sqlite shouldn't be used with Microsoft.EntityFrameworkCore.Sqlite.NetTopologySuite

[rboeters]

Silent crash EF Core 9.0 with Net Topology Suite / Spatiallite on Linux

The following crashes silently on Linux (Ubuntu 20.04) or Docker image mcr.microsoft.com/dotnet/runtime:8.0.

Minimal reproducable code

Program.cs:

using Microsoft.EntityFrameworkCore;
using NetTopologySuite.Geometries;

// Create in memory sqlite context
var context = new TestContext(
    (new DbContextOptionsBuilder<TestContext>())
        .UseSqlite($"Data Source=test.db", 
        x => x.UseNetTopologySuite()).Options);

context.Database.EnsureCreated();

var neverReached = 0; // This line is never reached, crashes silently on Docker linux (mcr.microsoft.com/dotnet/runtime:8.0)

internal class TestContext(DbContextOptions<TestContext> options) : DbContext(options)
{
    public DbSet<TestEntity> TestEntities { get; set; }
}

internal class TestEntity
{
    public int Id { get; set; }

    public string Name { get; set; }

    public Point Geom { get; set; }
}

csproj:

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

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net8.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
    <DockerDefaultTargetOS>Linux</DockerDefaultTargetOS>
    <DockerfileContext>.</DockerfileContext>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="9.0.0" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="9.0.0" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite.NetTopologySuite" Version="9.0.0" />
    <PackageReference Include="Microsoft.VisualStudio.Azure.Containers.Tools.Targets" Version="1.21.0" />
  </ItemGroup>

</Project>

Dockerfile (default generated Dockerfile with Spatiallite installed):

# See https://aka.ms/customizecontainer to learn how to customize your debug container and how Visual Studio uses this Dockerfile to build your images for faster debugging.

# This stage is used when running from VS in fast mode (Default for Debug configuration)
FROM mcr.microsoft.com/dotnet/runtime:8.0 AS base
RUN apt-get update && apt-get install -y libsqlite3-mod-spatialite # Install spatiallite
USER $APP_UID
WORKDIR /app


# This stage is used to build the service project
FROM mcr.microsoft.com/dotnet/sdk:8.0 AS build
ARG BUILD_CONFIGURATION=Release
WORKDIR /src
COPY ["EfCore9Test.csproj", "."]
RUN dotnet restore "./EfCore9Test.csproj"
COPY . .
WORKDIR "/src/."
RUN dotnet build "./EfCore9Test.csproj" -c $BUILD_CONFIGURATION -o /app/build

# This stage is used to publish the service project to be copied to the final stage
FROM build AS publish
ARG BUILD_CONFIGURATION=Release
RUN dotnet publish "./EfCore9Test.csproj" -c $BUILD_CONFIGURATION -o /app/publish /p:UseAppHost=false

# This stage is used in production or when running from VS in regular mode (Default when not using the Debug configuration)
FROM base AS final
WORKDIR /app
COPY --from=publish /app/publish .
ENTRYPOINT ["dotnet", "EfCore9Test.dll"]

Output

It crashes silently, but here is the output:

Loaded '/usr/share/dotnet/shared/Microsoft.NETCore.App/8.0.11/System.Private.CoreLib.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/app/bin/Debug/net8.0/EfCore9Test.dll'. Symbols loaded.
Loaded '/usr/share/dotnet/shared/Microsoft.NETCore.App/8.0.11/System.Runtime.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/HotReloadAgent/Microsoft.Extensions.DotNetDeltaApplier.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/usr/share/dotnet/shared/Microsoft.NETCore.App/8.0.11/System.IO.Pipes.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/usr/share/dotnet/shared/Microsoft.NETCore.App/8.0.11/System.Linq.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/usr/share/dotnet/shared/Microsoft.NETCore.App/8.0.11/System.Collections.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/usr/share/dotnet/shared/Microsoft.NETCore.App/8.0.11/System.Console.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/usr/share/dotnet/shared/Microsoft.NETCore.App/8.0.11/System.Memory.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/usr/share/dotnet/shared/Microsoft.NETCore.App/8.0.11/System.Threading.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/usr/share/dotnet/shared/Microsoft.NETCore.App/8.0.11/System.Net.Sockets.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/usr/share/dotnet/shared/Microsoft.NETCore.App/8.0.11/System.Net.Primitives.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/usr/share/dotnet/shared/Microsoft.NETCore.App/8.0.11/Microsoft.Win32.Primitives.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/usr/share/dotnet/shared/Microsoft.NETCore.App/8.0.11/System.Diagnostics.Tracing.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/usr/share/dotnet/shared/Microsoft.NETCore.App/8.0.11/System.Threading.ThreadPool.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/usr/share/dotnet/shared/Microsoft.NETCore.App/8.0.11/System.Threading.Thread.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/usr/share/dotnet/shared/Microsoft.NETCore.App/8.0.11/System.Collections.Concurrent.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/usr/share/dotnet/shared/Microsoft.NETCore.App/8.0.11/System.Runtime.InteropServices.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/usr/share/dotnet/shared/Microsoft.NETCore.App/8.0.11/System.Runtime.Loader.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/app/bin/Debug/net8.0/Microsoft.EntityFrameworkCore.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/usr/share/dotnet/shared/Microsoft.NETCore.App/8.0.11/System.ComponentModel.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/app/bin/Debug/net8.0/Microsoft.EntityFrameworkCore.Sqlite.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/app/bin/Debug/net8.0/Microsoft.EntityFrameworkCore.Relational.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/app/bin/Debug/net8.0/Microsoft.Extensions.DependencyInjection.Abstractions.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/usr/share/dotnet/shared/Microsoft.NETCore.App/8.0.11/System.Collections.Immutable.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/usr/share/dotnet/shared/Microsoft.NETCore.App/8.0.11/System.Data.Common.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/usr/share/dotnet/shared/Microsoft.NETCore.App/8.0.11/System.ComponentModel.Primitives.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/app/bin/Debug/net8.0/Microsoft.Extensions.Logging.Abstractions.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/app/bin/Debug/net8.0/Microsoft.Extensions.Caching.Abstractions.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/app/bin/Debug/net8.0/Microsoft.EntityFrameworkCore.Sqlite.NetTopologySuite.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/app/bin/Debug/net8.0/Microsoft.Extensions.DependencyInjection.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/app/bin/Debug/net8.0/System.Diagnostics.DiagnosticSource.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/usr/share/dotnet/shared/Microsoft.NETCore.App/8.0.11/System.Linq.Expressions.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/app/bin/Debug/net8.0/Microsoft.EntityFrameworkCore.Abstractions.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/usr/share/dotnet/shared/Microsoft.NETCore.App/8.0.11/System.Transactions.Local.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/app/bin/Debug/net8.0/NetTopologySuite.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/usr/share/dotnet/shared/Microsoft.NETCore.App/8.0.11/netstandard.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/app/bin/Debug/net8.0/Microsoft.Extensions.Logging.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/app/bin/Debug/net8.0/Microsoft.Extensions.Options.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/usr/share/dotnet/shared/Microsoft.NETCore.App/8.0.11/System.ComponentModel.TypeConverter.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/usr/share/dotnet/shared/Microsoft.NETCore.App/8.0.11/System.Reflection.Emit.ILGeneration.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/usr/share/dotnet/shared/Microsoft.NETCore.App/8.0.11/System.Reflection.Emit.Lightweight.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/usr/share/dotnet/shared/Microsoft.NETCore.App/8.0.11/System.Reflection.Primitives.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded 'Anonymously Hosted DynamicMethods Assembly'. 
Loaded '/app/bin/Debug/net8.0/System.Text.Json.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/app/bin/Debug/net8.0/Microsoft.Extensions.Configuration.Abstractions.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/app/bin/Debug/net8.0/Microsoft.Extensions.Caching.Memory.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/app/bin/Debug/net8.0/Microsoft.Extensions.Primitives.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/app/bin/Debug/net8.0/Microsoft.Data.Sqlite.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/app/bin/Debug/net8.0/SQLitePCLRaw.core.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/app/bin/Debug/net8.0/SQLitePCLRaw.batteries_v2.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/app/bin/Debug/net8.0/SQLitePCLRaw.provider.e_sqlite3.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/usr/share/dotnet/shared/Microsoft.NETCore.App/8.0.11/System.ComponentModel.Annotations.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/app/bin/Debug/net8.0/NetTopologySuite.IO.SpatiaLite.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/usr/share/dotnet/shared/Microsoft.NETCore.App/8.0.11/System.ObjectModel.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/usr/share/dotnet/shared/Microsoft.NETCore.App/8.0.11/System.Text.RegularExpressions.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/app/bin/Debug/net8.0/Microsoft.Extensions.DependencyModel.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Loaded '/usr/share/dotnet/shared/Microsoft.NETCore.App/8.0.11/System.Runtime.InteropServices.RuntimeInformation.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
The program 'dotnet' has exited with code 0 (0x0).

Include provider and version information

EF Core version: 9.0.0
Database provider: Microsoft.EntityFrameworkCore.Sqlite 9.0.0
Target framework: .NET 8.0
Operating system: Linux (Ubuntu 20.04) or Docker image (mcr.microsoft.com/dotnet/runtime:8.0)

[Zetanova]

This behavior/crash still happens with ef core 9.0.9 and runtime:8.0-noble

There are two things to fix the crash:

1. the SQLitePCLRaw.bundle_e_sqlite3 included package in Microsoft.EntityFrameworkCore.Sqlite need to be added explicitly in the project. I didn't discover exactly why it does not work with the nested package.

2. The LD_LIBRARY_PATH need to be set. The default dotnet env does not look into the linux lib directories by default.
   example: ENV LD_LIBRARY_PATH="/usr/lib/x86_64-linux-gnu:/usr/lib"

The result versions of the sqlite inside the container would currently be
SQLite version: 3.50.4
spatialite version: 5.1.0

To use the native linux dist sqlite lib it need it need to be installed with libsqlite3-0
and because the file name libsqlite3.so.0 is not searched for it need to be linked
with RUN ln -s /usr/lib/x86_64-linux-gnu/libsqlite3.so.0 /usr/lib/libsqlite3.so
and the package SQLitePCLRaw.bundle_sqlite3 need to be used (without e in the name)

This will produce a version in runtime:8.0-noble
SQLite version: 3.45.1 (lower then nuget package)
spatialite version: 5.1.0

If the project need to be run also on windows then a conditional package can to be included with:

    <ItemGroup Condition="'$(OS)' == 'Windows_NT'">
	    <PackageReference Include="SQLitePCLRaw.bundle_e_sqlite3" Version="3.0.2" />
    </ItemGroup>

Windows Host:
SQLite version: 3.50.4
spatialite version: 4.3.0a

[Zetanova]

I narrowed down the issue.

The bundled sqlite lib "e_sqlite3" does not work well with mod_spatialite under linux/docker

if both packages SQLitePCLRaw.bundle_e_sqlite3 and SQLitePCLRaw.bundle_sqlite3 are added,
then the trick is to explicitly use the SQLitePCLRaw.bundle_sqlite3 to load the native libsqlite3.so.0

The DbContext will call SQLitePCL.Batteries.Init() and LoadExtension('mod_spatialite') for all connections
even if the native lib was loaded and used before and working fine.

The SQLitePCL.Batteries.Init() will reset the SQLitePCL.raw.SetProvider provider back to the wrong SQLite3Provider_e_sqlite3 provider and this will make 1/3 malock/module not found and other sudden term issues.

To prevent this behavior the SQLitePCL.raw.SetProvider need to be frozen.

Working code snipped:

if (OperatingSystem.IsLinux())
{
    NativeLibrary.SetDllImportResolver(typeof(SQLite3Provider_sqlite3).Assembly, DllImportResolver);

    //prevent loading of bundled libe_sqlite3.so
    NativeLibrary.SetDllImportResolver(typeof(SQLite3Provider_e_sqlite3).Assembly, DllImportResolver);
    
    static IntPtr DllImportResolver(string libraryName, Assembly assembly, DllImportSearchPath? searchPath)
    {
        if (libraryName == "sqlite3")
            return NativeLibrary.Load("libsqlite3.so.0", assembly, searchPath);
    
        if (libraryName == "e_sqlite3")
            throw new DllNotFoundException("e_sqlite3 not usable with mod_spatialite");
        
        return IntPtr.Zero;
    }
    
    SQLitePCL.raw.SetProvider(new SQLitePCL.SQLite3Provider_sqlite3());
    SQLitePCL.raw.FreezeProvider(true); //important
}

In the base plane of the dockerfile only the native libs need to be installed:

FROM mcr.microsoft.com/dotnet/runtime:8.0-noble AS base

# Add system dependencies
RUN apt-get update && apt-get install -y \
    libsqlite3-0 \
    libsqlite3-mod-spatialite \
    && apt-get clean \
    && rm -rf /var/lib/apt/lists/*

USER $APP_UID
WORKDIR /app

[AndriySvyryd]

@ericsink any pointers on how this experience can be improved?

[ericsink]

Well that's a mess, isn't it?

No suggestions right now, but I've been thinking about trying to set up an easy path for using spatialite. It's good to see this case.

[ericsink]

Actually, one tip:

SQLitePCLRaw's bundles are designed to make very common cases easy, but for anything even a little bit complicated, they mostly make things harder.

It's usually better to switch to the package variant that references SQLitePCLRaw.core only, and not the bundle. It's called Microsoft.Data.Sqlite.Core, I think.

Then do the initialization yourself with SetProvider().

But there are a number of details in this thread that I haven't investigated right now specifically, so consider this a high-level remark.