Tracing "SQLite error 19 foreign key constraint" down to what actually causes it?

Question

See screenshots.

How do you find out which entity or which record produces this error?

Haven't found info in the Internet.

Default and test data is added using FluentApi in the configuration classes.

The database is EnsureDeleted() first, then when it's EnsureCreated() the exception is raised.

Answer 1

SQLite is a database that executes sql code. When you call update on EF it generates all the sql text and passes to the SQLite library. while executing the sql text, SQLIte detects a consistency error and throws an error. The breakpoint will always be the EF update Statement (SaveChanges, etc).

if you are using EF core you can add logging so you can see the generated sql:

https://learn.microsoft.com/en-us/ef/core/logging-events-diagnostics/simple-logging

if you know c, you can use a debug build of SQLite, and use the visual studio native code debugger:

https://www.sqlite.org/debugging.html

Answer 2

Here's how I figured out my foreign key problem. In our integration tests, we use SQLite as a substitute for SQL Server. What was running fine in SQL Server just wouldn't work in SQLite.

Step 1 - allow FK constraints to be broken

using var command = _context.Database.GetDbConnection().CreateCommand();
command.CommandText = "PRAGMA foreign_keys = false;";
_context.Database.OpenConnection();
command.ExecuteNonQuery();

Step 2 - save the changes that are breaking your FK

_context.SaveChanges();

Step 3 - find out what the errors are

This is a pretty clunky way of doing it, but it gave me the answer.

using var command = _context.Database.GetDbConnection().CreateCommand();
command.CommandText = "PRAGMA foreign_key_check;";
_context.Database.OpenConnection();
using var reader = command.ExecuteReader();

while (reader.Read())
{
var colCount = reader.VisibleFieldCount;
for (int i = 0; i < colCount; i++)
{
var col = reader.GetValue(i);
} // put a breakpoint here and examine what's in "col"
}