no such function: InitSpatialMetaData

[tmutton]

I have two projects, first one is MVC second is for tests. I have replicated the problem in a repo here: https://github.com/tmutton/EF_Sqlite_IPoint_Problem - Sqlite has been chosen for the test project for its inmemory capabilities.

The tests use AppDbContext (the same as the main project) with UseNetTopologySuite as required. When the tests run I get the following error. When the running the code on the main project (which normally uses SqlServer it works fine. Therefore I'm thinking this is an Sqlite issue.

Can anyone advise a solution?

Test Name:  EF_Sqlite_IPoint_Problem.Tests.VenueTests.Should_Create_New_Venue
Test FullName:  EF_Sqlite_IPoint_Problem.Tests.VenueTests.Should_Create_New_Venue
Test Source:    c:\code\EF_Sqlite_IPoint_Problem\EF_Sqlite_IPoint_Problem.Tests\VenueTests.cs : line 12
Test Outcome:   Failed
Test Duration:  0:00:00.4

Result StackTrace:  
at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteCommand.PrepareAndEnumerateStatements(Stopwatch timer)+MoveNext()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Storage.RelationalDatabaseCreator.CreateTables()
   at Microsoft.EntityFrameworkCore.Storage.RelationalDatabaseCreator.EnsureCreated()
   at Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade.EnsureCreated()
   at EF_Sqlite_IPoint_Problem.Tests.VenueTests.Should_Create_New_Venue() in c:\code\EF_Sqlite_IPoint_Problem\EF_Sqlite_IPoint_Problem.Tests\VenueTests.cs:line 23
--- End of stack trace from previous location where exception was thrown ---
Result Message: Microsoft.Data.Sqlite.SqliteException : SQLite Error 1: 'no such function: InitSpatialMetaData'.

Steps to reproduce

Checkout solution here: https://github.com/tmutton/EF_Sqlite_IPoint_Problem

dotnet restore run the test "Should_Create_New_Venue" in VenueTests in the Tests project

Further technical details

Dotnet Core: 2.2.102 EF Core version: 2.2.1 Database Provider: (Microsoft.EntityFrameworkCore.Sqlite.Core 2.2.1) with Microsoft.EntityFrameworkCore.Sqlite.NetTopologySuite (v 2.2.1) Operating system: Windows 10 Home (1803 -- Build 17134.523) IDE: (e.g. Visual Studio 2017 15.9.4)

[ajcvickers]

Note for triage: Test code:

[Fact]
public async Task Should_Create_New_Venue()
{
    using (var connection = new SqliteConnection("DataSource=:memory:"))
    {
        connection.Open();

        var options = new DbContextOptionsBuilder<ApplicationDbContext>().UseSqlite(connection, x => x.UseNetTopologySuite()).Options;

        // ensure the database was created
        using (var context = new ApplicationDbContext(options))
        {
            context.Database.EnsureCreated();
        }
    }
}

Context:

public class ApplicationDbContext : IdentityDbContext
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
        : base(options)
    {
    }
    protected override void OnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder);
    }

    public DbSet<Venue> Venues { get; set; }
}

[smitpatel]

Wrong sqlite3?

[ajcvickers]

@bricelam to follow up.

[bricelam]

If EF doesn't open the connection, you'll need to load SpatiaLite yourself:

using Microsoft.EntityFrameworkCore.Infrastructure;

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

[bricelam]

This method searches for the native mod_spatialite library and executes the following.

SELECT load_extension('mod_spatialite');

After adding it, the InitSpatialMetaData function should be available.

[tmutton]

Hi @bricelam I think we're on the right track with your solution but I'm now receiving a "not authorized" error from SQLite. I assume this is not related?

Test Name: EF_Sqlite_IPoint_Problem.Tests.VenueTests.Should_Create_New_Venue Test FullName: EF_Sqlite_IPoint_Problem.Tests.VenueTests.Should_Create_New_Venue Test Source: c:\code\EF_Sqlite_IPoint_Problem\EF_Sqlite_IPoint_Problem.Tests\VenueTests.cs : line 13 Test Outcome: Failed Test Duration: 0:00:00.14

Result StackTrace:
at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db) at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.Data.Sqlite.SqliteCommand.ExecuteNonQuery() at Microsoft.EntityFrameworkCore.Infrastructure.SpatialiteLoader.Load(DbConnection connection) at EF_Sqlite_IPoint_Problem.Tests.VenueTests.Should_Create_New_Venue() in c:\code\EF_Sqlite_IPoint_Problem\EF_Sqlite_IPoint_Problem.Tests\VenueTests.cs:line 18 --- End of stack trace from previous location where exception was thrown --- Result Message: Microsoft.Data.Sqlite.SqliteException : SQLite Error 1: 'not authorized'.

Any thoughts?

[ajcvickers]

@tmutton Could you file a new issue for this, including a small, runnable project/solution or complete code listing that demonstrates how to reproduce the behavior?