How to ignore Foreign Key Constraints in Entity Framework Core SQLite database?

Question

Foreign Key constraint failed use SQLite with Entity Framework Core

I have relations in table

[Table("organizations")]
public class Organizations
{
    [Column("id")]
    public int Id { get; set; }

    [Column("parent_id")]
    [ForeignKey("id")]
    public int? ParentId { get; set; }

    [Column("person_id")]
    public int? PersonId { get; set; }
 }


public DbSet<Organizations> Organizations { get; set; }

using (var db = new SQLiteDbContext($"Filename={dbPath};"))
{
    db.Database.ExecuteSqlCommand("PRAGMA foreign_keys=OFF;");
    db.Database.ExecuteSqlCommand("PRAGMA ignore_check_constraints=true;");
    db.Organizations.AddRange(organizationsResult.Rows);
}

I get an error from the Sqlite database:

{"SQLite Error 19: 'FOREIGN KEY constraint failed'"}

Answer 1

The PRAGMA looses effect if the connection is closed. You need to increase the lifetime of the connection by calling db.Database.OpenConnection() and CloseConnection() before and after.

You can also call optionsBiulder.UseSqlite(connectionString, x => x.SuppressForeignKeyEnforcement()) to prefent EF from automatically turning foreign key enforcement on per connection.

Entity Framework Core 3 Update:

For EF Core 3.0 SuppressForeignKeyEnforcement has been removed. see docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.0/…

Use "Foreign Keys = False" in the connection string instead. Eg.

connectionString = "Data Source=Data.db;Password=yourpassword;Foreign Keys=False"