EFCore SQLite database substitutes its own table name for mine


I'm building a .Net Core 3.1 desktop application using EFCore 3.1 and the SQLite provider.

My problem is that the SQLite Provider keeps throwing an exception, telling me that table ValidationTargetTypes2 does not exist. But I'm not using that table name in my queries. I'm using ValidationTargetTypes (Note the lack of "2" suffix). And that table does exist.

I turned on logging and it verifies that the correct table name is being used. Here is the complete log output of my SQL Command. Note that I only use the correct table name ValidationTargetTypes. But the exception still complains that ValidationTargetTypes2 not existing

Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (1ms) [Parameters=[@__target_Serial_0='A500' (Size = 4)], CommandType='Text', CommandTimeout='30']
SELECT "v"."Id", "v"."FileName", "v"."IsDeleted", "v"."Name", "v"."Serial", "v"."ValidationTargetTypeId"
FROM "ValidationTargets" AS "v"
WHERE NOT ("v"."IsDeleted") AND ("v"."Serial" = @__target_Serial_0)
Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (1ms) [Parameters=[@__target_Part_0='RSP-VC01' (Size = 8)], CommandType='Text', CommandTimeout='30']
SELECT "v"."Id", "v"."Name", "v"."Part"
FROM "ValidationTargetTypes" AS "v"
WHERE "v"."Part" = @__target_Part_0
Microsoft.EntityFrameworkCore.Database.Command: Error: Failed executing DbCommand (2ms) [Parameters=[@p0='ValidationTarget_RSP-VC01_A500' (Size = 30), @p1='False' (DbType = String), @p2='A500' (Size = 4), @p3='A500' (Nullable = false) (Size = 4), @p4='1' (DbType = String)], CommandType='Text', CommandTimeout='30']
INSERT INTO "ValidationTargets" ("FileName", "IsDeleted", "Name", "Serial", "ValidationTargetTypeId")
VALUES (@p0, @p1, @p2, @p3, @p4);
FROM "ValidationTargets"
WHERE changes() = 1 AND "rowid" = last_insert_rowid();

And here, is the error that then immediately appears. Note the substituted table name.

Microsoft.EntityFrameworkCore.Update: Error: An exception occurred in the database while saving changes for context type 'GelSight.Mobile.Data.GelSightContext'.
Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
 ---> Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'no such table: main.ValidationTargetTypes2'.
   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteCommand.PrepareAndEnumerateStatements(Stopwatch timer)+MoveNext()
   at Microsoft.Data.Sqlite.SqliteCommand.GetStatements(Stopwatch timer)+MoveNext()

Somewhere, somehow that wrong table name with the "2" suffix is being substituted. Where can it be happening? Nowhere in my entire codebase does the string ValidationTargetTypes2 even appear. I searched every file type (".") for that string. Nope.

Also nowhere in the actual live database do I see anything by that name.

If I delete the database file and re-run my app (which calls Migrate at startup to ensure it exists), then the query works just fine. So I guess the problem must be within the database file.

I've had about 4 migrations on this database. Clearly I've caused this problem. At one point I did briefly have a table by that name (with the "2" suffix) because I had to work around a problem caused by the fact that Sqlite cannot drop columns. I followed Microsoft recommendations fixing it. But clearly my temporary rename has left some detritus around. The question is, where?

Is there any way for me to edit this database manually to fix this? I really hope to avoid deleting it. I don't know where in the database to go. I don't see anything in the MigrationHistory that stands out

asked on Stack Overflow May 12, 2020 by Joe • edited May 12, 2020 by Joe

0 Answers

Nobody has answered this question yet.

User contributions licensed under CC BY-SA 3.0