Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQLite EF Core - 'FOREIGN KEY constraint failed'

In my ASP.Net Core project I am getting this exception by starting, becuase I am initializing the database in runtime:

fail: Microsoft.EntityFrameworkCore.Update[10000]

An exception occurred in the database while saving changes for context type 'BlazorWeb.Server.Data.SQLiteTestDbContext'.
Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.

Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 19: 'FOREIGN KEY constraint failed'.

at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
....

My models:

public class ObjectModel : BaseEntity
{
    [Key]
    public int Id { get; set; }
    public String Name { get; set; }
    public String PreviewImage { get; set; }
    public String  TagName { get; set; }

    // Foreign keys---
    public int TypeId { get; set; }
    public ObjectTypeModel TypeModel { get; set; }
    //---
}

public class ObjectTypeModel : BaseEntity
{
    [Key]
    public int TypeId { get; set; }
    public String Name { get; set; }
    public String PreviewImage { get; set; }
    public String TagName { get; set; }

    public ICollection<ObjectModel> Objects { get; set; }
}

My SQLite OnModelCreating method in the DbContext:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<ObjectModel>()
      .HasOne<ObjectTypeModel>(d => d.TypeModel)
      .WithMany(dm => dm.Objects)
      .HasForeignKey(dkey => dkey.TypeId);
}

From the auto generated migration class:

            name: "Objects",
            columns: table => new
            {
                Id = table.Column<int>(nullable: false)
                    .Annotation("Sqlite:Autoincrement", true),
                CreatedOn = table.Column<DateTime>(nullable: false),
                ModifiedOn = table.Column<DateTime>(nullable: false),
                Name = table.Column<string>(nullable: true),
                PreviewImage = table.Column<string>(nullable: true),
                TagName = table.Column<string>(nullable: true),
                TypeId = table.Column<int>(nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Objects", x => x.Id);
                table.ForeignKey(
                    name: "FK_Objects_ObjectTypes_TypeId",
                    column: x => x.TypeId,
                    principalTable: "ObjectTypes",
                    principalColumn: "TypeId",
                    onDelete: ReferentialAction.Cascade);
            });

In my initialize method I am only filling it with some data and saving these to the context. But the database never receives these records and also the foreign key constraint exception occurs.

Can anyone help me - am I doing something wrong?

EDIT I have found out that exception occurs after the SaveChangeAsync() method runtime. My overriden SaveChangesAsyn() method:

     public Task<int> SaveChangesAsync()
     {
      var entries = ChangeTracker
            .Entries()
            .Where(e => e.Entity is BaseEntity && (
                    e.State == EntityState.Added ||
                    e.State == EntityState.Modified));

            foreach (var entityEntry in entries)
            {
                // overwrite `ModifiedOn` value with current date.
                ((BaseEntity)entityEntry.Entity).ModifiedO=
                 DateTime.Now; 

                // set `CreatedOn` to current date if its not already set. 
                if (entityEntry.State == EntityState.Added)
                {
                    ((BaseEntity)entityEntry.Entity).CreatedOn = DateTime.Now;
                }
            }
            return base.SaveChangesAsync();
        }
like image 869
brstkr Avatar asked Jun 28 '20 17:06

brstkr


2 Answers

I had the same error trying to delete one record with a relation one-to-many with another object.

The problem was that in the DataContext the deletion on cascade was not defined, you can add to your DataContext this:

            
 modelbuilder.Entity<ObjectTypeModel>()
                .HasMany(dm => dm.Objects)
                .WithOne()
                .OnDelete(DeleteBehavior.Cascade);
like image 185
Cristian Hoger Avatar answered Nov 03 '22 11:11

Cristian Hoger


Your models and migration are corrected. The main reason is when SaveChangesAsyn.

 TypeId = table.Column<int>(nullable: false)

From here we can see, FK(TypeId) is not nullable. So you should confirm your FK(TypeId of ObjectType) is not null and existed when you add new Object.

Here is my code to add Object and models are same as you.

public async Task<IActionResult> Create([Bind("Id,content,Deadline,IsComplete,UserId")] ToDoItem toDoItem)
        {
            if (ModelState.IsValid)
            {
                _context.Add(toDoItem);
                await _context.SaveChangesAsync();

            }
            return View();
        }

The codes of models is below.

public class ToDoItem
    {
        public int Id { get; set; }
        public string content { get; set; }
        public DateTime Deadline { get; set; }
        public bool IsComplete { get; set; }

        public int UserId { get; set; }
        public User user { get; set; }
    }

public class User
    {
        public int Id { get; set; }
        public string username { get; set; }
        public string password { get; set; }
        public ICollection<ToDoItem> ToDoItems { get; set; }
    }
like image 39
Michael Wang Avatar answered Nov 03 '22 10:11

Michael Wang