Foreign Key Constraint Failure SQLite and .NET Core Web Api

0

I'm getting a foreign key constraint error when I try to post a new resource to my .NET Core 2.2 web api controller but I can't figure out which field is failing specifically. I'm using Postman to test the api.

I have a model in my C# .NET Core Web API 2.2 project.

public class Video
    {
        public int Id { get; set; }

        [Required]
        public string Title { get; set; }

        [Required]
        public string Url { get; set; }

        [Required]
        public string Description { get; set; }

        [Required]
        public string DurationInMinutes { get; set; }

        [Required]
        public VideoAccessType VideoAccessLevel { get; set; }

        public DateTime DateAdded { get; set; }

        [Required]
        public int VideoTypeId { get; set; }
        public VideoType VideoType { get; set; }

        [Required]
        public int VideoCategoryId { get; set; }
        public VideoCategory VideoCategory { get; set; }
    }

My repo uses entity framework to update the database.

public async Task<Video> InsertVideoAsync(Video video)
{
    video.DateAdded = DateTime.Now;
    _dbContext.Videos.Add(video);
    await _dbContext.SaveChangesAsync();

    return video;
}

And here's my controller action:

[HttpPost]
        public async Task<ActionResult> CreateVideo([FromBody]Video video)
        {
            if (!ModelState.IsValid)
            {
                return BadRequest(new { Status = false, ModelState = ModelState });
            }

            try
            {
                var newVideo = await _videosRepo.InsertVideoAsync(video);
                if (newVideo == null)
                {
                    return BadRequest(new { Status = false });
                }
                return CreatedAtRoute("GetVideoRoute", new { id = newVideo.Id },
                        new { Status = true, Video = newVideo });
            }
            catch (Exception)
            {
                return BadRequest(new { Status = false });
            }
        }

enter image description here

Here is the error in the console from SQLite (version 2.2.4)

Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (9ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      PRAGMA foreign_keys=ON;
fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (7ms) [Parameters=[@p0='?', @p1='?' (Size = 23), @p2='?' (Size = 2), @p3='?' (Size = 14), @p4='?' (Size = 14), @p5='?', @p6='?', @p7='?'], CommandType='Text', CommandTimeout='30']
      INSERT INTO "Videos" ("DateAdded", "Description", "DurationInMinutes", "Title", "Url", "VideoAccessLevel", "VideoCategoryId", "VideoTypeId")
      VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7);
      SELECT "Id"
      FROM "Videos"
      WHERE changes() = 1 AND "Id" = last_insert_rowid();
Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 19: 'FOREIGN KEY constraint failed'.
   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteAsync(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues, CancellationToken cancellationToken)
fail: Microsoft.EntityFrameworkCore.Update[10000]
      An exception occurred in the database while saving changes for context type 'fitclubAPI.Data.ApplicationDbContext'.
      Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> Microsoft.Data.Sqlite.SqliteException: SQLite Error 19: 'FOREIGN KEY constraint failed'.
         at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
         at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
         at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
         at Microsoft.Data.Sqlite.SqliteCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteAsync(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
         --- End of inner exception stack trace ---
         at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(DbContext _, ValueTuple`2 parameters, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IReadOnlyList`1 entriesToSave, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> Microsoft.Data.Sqlite.SqliteException: SQLite Error 19: 'FOREIGN KEY constraint failed'.
   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteAsync(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
   --- End of inner exception stack trace ---
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(DbContext _, ValueTuple`2 parameters, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IReadOnlyList`1 entriesToSave, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
info: Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker[2]
      Executed action method fitclubAPI.Controllers.VideosController.CreateVideo (fitclubAPI), returned result Microsoft.AspNetCore.Mvc.BadRequestObjectResult in 713.0621ms.
info: Microsoft.AspNetCore.Mvc.Infrastructure.ObjectResultExecutor[1]
      Executing ObjectResult, writing value of type '<>f__AnonymousType2`1[[System.Boolean, System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e]]'.
info: Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker[2]
      Executed action fitclubAPI.Controllers.VideosController.CreateVideo (fitclubAPI) in 1085.8393ms
info: Microsoft.AspNetCore.Hosting.Internal.WebHost[2]
      Request finished in 1181.4884ms 400 application/json; charset=utf-8
c#
.net
entity-framework
sqlite
asp.net-core
asked on Stack Overflow May 23, 2019 by J.G.Sable

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0