ErikEJ / EFCorePowerTools

Entity Framework Core Power Tools - reverse engineering, migrations and model visualization in Visual Studio & CLI
MIT License
2.2k stars 299 forks source link

Reverse engineering a SQLite database with spatialite tables fails #2571

Open andrerav opened 1 month ago

andrerav commented 1 month ago

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:

image

5: Observe error message:

image

Provide technical details

ErikEJ commented 1 month ago

@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 commented 1 month ago

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

ErikEJ commented 1 month ago

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

ErikEJ commented 1 month ago

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 commented 1 month ago

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 commented 1 month ago

@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 commented 1 month ago

@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 commented 1 month ago

@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:

image

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

ErikEJ commented 2 weeks ago

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