CODEWITHSUNDEEP
asp.net-coreinsert
Chris Hadfield
Chris Hadfield

Reputation: 524

Microsoft.EntityFrameworkCore.DbUpdateException

I have a comment table where it have a two foreign key(i.e Userid and postid). I was trying to insert data into comment table using these two foreign key but was unable to insert. This is my post table

public class Post
  {
    public int Id { get; set; }
    public string PostText { get; set; }
    public string Title { get; set; }
    public bool Status { get; set; }
    public DateTime PostDate { get; set; }
    public virtual List<Comment> Comments { get; set; }
    public ApplicationUser ApplicationUser { get; set; }
  }

And this is my comment table

public class Comment
  {
    public int Id { get; set; }
    public string CommentText { get; set; }
    public DateTime CommentTime { get; set; }
    public bool Status { get; set; }
    public ApplicationUser CommentBy { get; set; }
    public Post Posts { get; set; }
  }

Comment service

public void Save(Comment comment)
    {
      _context.Set<Comment>().Add(comment);
      _context.SaveChanges();
    }

And this is my controller

[HttpPost]
    public ObjectResult SaveComment([FromBody] Comment comment)
    {
      if (ModelState.IsValid)
      {
        try
        {
          _commentService.Save(comment);
          return Ok("comment saved");
        } catch (Exception e)
        {
          return BadRequest(e);
        }
      } else
      {
        return BadRequest("Model is not valid");
      }
    }

And the error is

{Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'PK_AspNetUsers'. Cannot insert duplicate key in object 'dbo.AspNetUsers'. The duplicate key value is (966fc417-8757-4bac-89b2-9975d4f2cd41).

Cannot insert explicit value for identity column in table 'Posts' when IDENTITY_INSERT is set to OFF.

The statement has been terminated.

this is my api requestenter image description here

Upvotes: 1

Views: 6846

Answers (1)

itminus
itminus

Reputation: 25360

The reason is that you will also insert a new comment.CommentBy when you're inserting a brand new comment:

// the `comment` here is constructed by model binding, which is a brand new entity
[HttpPost]
public ObjectResult SaveComment([FromBody] Comment comment)
{
    // ...
    _commentService.Save(comment); // save the brand new comment
    // ...
}

"A brand new comment" means this Comment entity and its related propreties are all untracked. When saving a brand new entity, EF Core will also create related entities for you automatically.

To avoid this behavior, you could mark the state of comment.CommentBy as Unchanged,

_context.Entry(comment.CommentBy).State= EntityState.Unchanged;

so that the EF Core will not create a new CommentBy (i.e. an ApplicationUser) for you. But be careful: you must make sure the ApplicationUser already exists.

The same goes Post.

Another approach is much safer. As suggested by @Khai Nguyen in the comment, you should get ApplicationUser and Post instance from database, so that the EF Core knows there's already a ApplicationUser and a Post within database and won't insert new ApplicationUser or Post for you.

Upvotes: 1

Related Questions