CODEWITHSUNDEEP
c#sqliteentity-framework-core
Maris
Maris

Reputation: 4776

EF core 6.0 + SQLite foreign key constraint, meaningful error description

I'm trying to cover a large project, with complex database schema, with extensive amount of integration tests. For integration tests, I'm using SQLite database. Each time when foreign key constraint is violated, I get an error:

FOREIGN KEY constraint failed

Stack: EntityFramework core 6.0, SQLite v 6.0

Connection string: Data Source=:memory:;foreign keys=true

Without explanation, which foreign key is it hit. In a case when entity is complex, it always takes anomalous amount of time to come up which constraint is it. Is there any way how to extend exception with information on which foreign key constraint has hit? Something like:

FOREIGN KEY constraint - ItemId failed

Upvotes: 8

Views: 1123

Answers (1)

Mark Benningfield
Mark Benningfield

Reputation: 2892

Left to itself, SQLite does not close out a transaction when a commit is attempted with foreign keys in conflict. At that point, you can use pragma commands to query the state of foreign keys in conflict, fix or remove them, then proceed with committing the transaction.

However, wrappers for SQLite usually dispose of a transaction as soon as an error is raised, rendering that solution untenable. In that case, you have to take control of the transaction the wrapper provides.

We're going to assume that you are aware of how to manage transactions in EFCore (external, cross-context, etc.) that fit your particular scenario. This example just uses a transaction within a single context.


using var context = new PersonContext();
using var transaction = context.Database.BeginTransaction();

try
{
  // set all foreign key enforcement to "DEFERRED"
  context.Database.ExecuteSql("PRAGMA defer_foreign_keys=1;");

  // various CRUD operations that may involve foreign key conflicts
  
    // read the foreign key information from the table-valued pragma functions
    // `foreign_key_check` and `foreign_key_list`
  string sql = @"select a.""table"" || '.'|| ""from"" from pragma_foreign_key_check a
                   inner join pragma_foreign_key_list(a.""table"") b on
                   b.id == a.fkid;";
                      
  var conflicts = context.Database
    .SqlQuery<string>(sql)
    .ToList();
        
    // the list will contains strings like "tableName.fkColumn"
    // handle conflicts if the list count > 0
    
    // go ahead and commit the transaction
  transaction.Commit();
}
catch (Exception)
{
  // TODO: Handle failure
}

See:
defer_foreign_keys
foreign_key_check
foreign_key_list

Upvotes: 3

Related Questions