Note SpatiaLite stability issues in docs

[enricobenedos]

Good morning,

we are not able to include our tests suite on the CI process using Docker (Ubuntu 20.04). It seems that there is an issue with SQLite with NetTopologySuite. Locally on Windows 10 with Visual Studio 2019 tests are executed with no issue.

public abstract class TestDbContext : IDisposable
    {
        private const string InMemoryConnectionString = "DataSource=:memory:";
        private readonly SqliteConnection _connection;

        protected readonly MyContext DbContext;

        protected TestDbContext()
        {
            _connection = new SqliteConnection(InMemoryConnectionString);
            _connection.Open();

            var options = new DbContextOptionsBuilder<MyContext>()
                    .UseSqlite(_connection, x => x.UseNetTopologySuite())
                    .Options;
            DbContext = new MyContext(options);
            DbContext.Database.EnsureCreated();

            InitData();
        }

        private void InitData()
        {
            //Add some data in order to make tests
        }

        public void Dispose() => _connection.Close();
    }

public class SpvTests : TestDbContext
    {
        [Fact]
        public async void Test1()
        {
            Assert.False(DbContext.Database.EnsureCreated());
        }
    }

FROM mcr.microsoft.com/dotnet/sdk:5.0-focal
WORKDIR /src
COPY ["MyProject.Tests/MyProject.Tests.csproj", "MyProject.Tests/"]
COPY ["MyProject/MyProject.csproj", "MyProject/"]
COPY ["MyProject.Data/MyProject.Data.csproj", "MyProject.Data/"]

RUN apt update && apt install libsqlite3-dev libsqlite3-mod-spatialite -y

RUN dotnet restore "MyProject.Tests/MyProject.Tests.csproj"

COPY . .
WORKDIR "/src/MyProject.Tests"

ENTRYPOINT ["dotnet", "test"]

Include stack traces

Include the full exception message and stack trace for any exception you encounter.

Use triple-tick fences for stack traces. For example:

Microsoft (R) Test Execution Command Line Tool Version 16.9.4
Copyright (c) Microsoft Corporation.  All rights reserved.

Starting test execution, please wait...
A total of 1 test files matched the specified pattern.
[xUnit.net 00:00:00.00] xUnit.net VSTest Adapter v2.4.3+1b45f5407b (64-bit .NET 5.0.6)
[xUnit.net 00:00:00.85]   Discovering: MyProject.Tests
[xUnit.net 00:00:00.89]   Discovered:  MyProject.Tests
[xUnit.net 00:00:00.89]   Starting:    MyProject.Tests
The active test run was aborted. Reason: Test host process crashed

Test Run Aborted.
     1>Done Building Project "/src/MyProject.Tests/MyProject.Tests.csproj" (VSTest target(s)) -- FAILED.

Build FAILED.
    0 Warning(s)
    0 Error(s)

Include version information

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

  <PropertyGroup>
    <TargetFramework>net5.0</TargetFramework>

    <IsPackable>false</IsPackable>

    <DockerDefaultTargetOS>Linux</DockerDefaultTargetOS>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="5.0.6" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite.NetTopologySuite" Version="5.0.6" />
    <PackageReference Include="Microsoft.NET.Test.Sdk" Version="16.9.4" />
    <PackageReference Include="Microsoft.VisualStudio.Azure.Containers.Tools.Targets" Version="1.10.13" />
    <PackageReference Include="xunit" Version="2.4.1" />
    <PackageReference Include="xunit.runner.visualstudio" Version="2.4.3">
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
      <PrivateAssets>all</PrivateAssets>
    </PackageReference>
    <PackageReference Include="coverlet.collector" Version="3.0.3">
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
      <PrivateAssets>all</PrivateAssets>
    </PackageReference>
  </ItemGroup>

  <ItemGroup>
    <ProjectReference Include="..\MyProject\MyProject.csproj" />
  </ItemGroup>

</Project>

[AndriySvyryd]

@bricelam to take a look

[bricelam]

Are you using the SQLitePCLRaw.bundle_e_sqlite3 package? Newer versions of PROJ are incompatible with anything besides the system version of SQLite. See Installing SpatiaLite in our docs for how to work around the issue.

[enricobenedos]

Hello @bricelam, I checked the Microsoft guide and now this is my situation:

...
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite.Core" Version="5.0.6" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite.NetTopologySuite" Version="5.0.6" />
    <PackageReference Include="Microsoft.NET.Test.Sdk" Version="16.7.1" />
    <PackageReference Include="Microsoft.VisualStudio.Azure.Containers.Tools.Targets" Version="1.10.13" />
    <PackageReference Include="SQLitePCLRaw.bundle_sqlite3" Version="2.0.4" />
    <PackageReference Include="xunit" Version="2.4.1" />
    <PackageReference Include="xunit.runner.visualstudio" Version="2.4.3">
...

Now it works on linux environment using Docker but it does not work anymore locally using Windows 10. Visual studio tell me that no SQLite DLL/bundle was found.

I see that in the documentation project file SQLitePCLRaw.bundle_sqlite3 is used, but you ask me if I'm using SQLitePCLRaw.bundle_e_sqlite3. Which is the correct one?

[bricelam]

It's ugly, but you could...

  <ItemGroup Condition="'$(OS)' == 'Windows_NT'">
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="5.0.6" />
  </ItemGroup>

  <ItemGroup Condition="'$(OS)' != 'Windows_NT'">
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite.Core" Version="5.0.6" />
    <PackageReference Include="SQLitePCLRaw.bundle_sqlite3" Version="2.0.4" />
  </ItemGroup>

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

Note, this requires you to build on the same OS that you're going to run on.

[bricelam]

Note, I raised this issue on the SpatiaLite mailing list a while ago, but nobody seemed to care. ☹

[enricobenedos]

Thank you @bricelam for your support. I will try as soon as possibile your csproj file strategy.

I'm sad that SpatiaLite team is not giving so much priority to this issue. It can be an idea to better document this problem on Microsoft docs?

[enricobenedos]

It's ugly, but you could...

  <ItemGroup Condition="'$(OS)' == 'Windows_NT'">
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="5.0.6" />
  </ItemGroup>

  <ItemGroup Condition="'$(OS)' != 'Windows_NT'">
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite.Core" Version="5.0.6" />
    <PackageReference Include="SQLitePCLRaw.bundle_sqlite3" Version="2.0.4" />
  </ItemGroup>

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

Note, this requires you to build on the same OS that you're going to run on.

Anyway this workaround works

[bricelam]

Hey cool, they've rediscovered the issue for themselves. Maybe we'll see some progress.