Reverse engineering a SQLite database with spatialite tables fails

[andrerav]

Provide steps to reproduce a bug

1. Create an empty sqlite database
2. Load the mod_spatialite extension: SELECT load_extension('mod_spatialite');
3. Initialize the spatial database: SELECT InitSpatialMetaData();
4. Reverse engineer using SQLite driver:

5: Observe error message:

Provide technical details

• EF Core Power Tools version: 2.6.497

• Exact Visual Studio version: Version 17.12.0 Preview 2.0

• Database engine: SQLite with Spatialite extension

• EF Core version in use: EF Core 8

• Is Handlebars templates used: no

• Is T4 templates used: no

• Is a SQL Server .dacpac used: no

[ErikEJ]

@andrerav First, EF Core Power Tools does not look at the code in your project.

Currently I have not enabled support for spatial with Sqlite, but I can investigate the possibility of doing this.

https://github.com/ErikEJ/EFCorePowerTools/wiki/Reverse-Engineering#mapping

[ErikEJ]

I implemented a fix for this in the latest daily build, would be grateful if you could try it out.

[ErikEJ]

@andrerav The dailiy build is taking longer than expected, I will keep you posted

[ErikEJ]

I was not able to do this, as the payload of my extension became unmanageable. I will revisit for EF Core 9, looks like the payload was smaller with that.

[andrerav]

Hi @ErikEJ,

I was unable to get around this using EF Power Tools, so I went back to dotnet ef dbcontext scaffold and manually specified the tables I wanted to scaffold (using multiple -t arguments) to circumvent the problem and only scaffold my own tables (and not any of the spatialite meta tables).

Geometry columns are correctly generated as well provided that the Microsoft.EntityFrameworkCore.Sqlite.NetTopologySuite package is installed in the EF project.

If I don't specify the tables manually I get the same error message using dotnet ef as with EF Power Tools.

I think that being able to simply filter/specify the tables I want to scaffold could go a long way as a workaround for this issue.

[ErikEJ]

@andrerav The GUI is there to show all tables, perhaps the CLI will work better for you, though you might have to create the initial config file using a non spatial scracth database file.

[ErikEJ]

@andrerav hang on, are you saying that the tool should avoid discovering the spatial system files as well (and then also support the spatial EF Core plugin) for this to work?

Can you tell me what the names of these table are?

[andrerav]

@ErikEJ If I create a new, empty Sqlite database, load the spatialite extension, and execute select InitSpatialMetadata();, I am left with these tables, indices and views:

A rather long list of triggers are created as well when initializing the spatial metadata.

[ErikEJ]

@andrerav Have you seen this: https://stackoverflow.com/questions/78937032/entity-framework-core-cannot-scaffold-sqlite-database-with-spatialite-microsof (DROP the KNN2 table)

[ErikEJ]

@andrerav Ping ^^

[andrerav]

Oops, apologies @ErikEJ! Yes, I can confirm that dropping the KNN2 table works. If other users come across this and consider dropping the KNN2 table as a non-option then manually specifying the tables to scaffold also works. I still think it would be nice if there was an option in dotnet ef to omit tables when scaffolding, though :)