CODEWITHSUNDEEP
c#sqliteentity-framework-core
themoretheless
themoretheless

Reputation: 163

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

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'"}

Upvotes: 14

Views: 21609

Answers (1)

bricelam
bricelam

Reputation: 30405

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 learn.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"

Upvotes: 28

Related Questions