Vlad
Vlad

Reputation: 233

Entity core Violation of PRIMARY KEY constraint error when update

There is typical many to many relationship between books and authors. I've got an error when I tried to update my authors. The full name of error is "SqlException: Violation of PRIMARY KEY constraint 'PK_BookAuthor'. Cannot insert duplicate key in object 'dbo.BookAuthor'. The duplicate key value is (2, 2)."

If I don't use UpdateAssociatedObject function, all is okay. But if I use it, I get the error when I try to save the changes.

 var parseBook = Mapper.Map<BookViewModel, Book>(viewModel);
        Book viewModelBook = parseBook;
        var book = db.Books.FindOne(viewModel.BookId);
        book.BookId = viewModelBook.BookId;
        book.BookName = viewModelBook.BookName;
        book.Genre = viewModelBook.Genre;
        book.Pages = viewModelBook.Pages;
        book.Publisher = viewModelBook.Publisher;
        db.SaveChanges();
        UpdateAssociatedObject(book, viewModelBook);
    }
    public void UpdateAssociatedObject(Book bookForUpdate, Book book)
    {
        bookForUpdate.BookAuthors = book.BookAuthors;
    //error is here
        db.SaveChanges();
    }

The value of viewModelBook after mapping: The value of BookAuthor property Models(some properties removed for brevity)

 public class Book
{
    public int BookId { get; set; }

    public List<BookAuthor> BookAuthors { get; set; }
}
public class Author
    {
        public int AuthorId { get; set; }
        public string AuthorName { get; set; }
        public List<BookAuthor> BookAuthors { get; set; }
    }
 public class BookAuthor
{
    public int BookId { get; set; }
    public Book Book { get; set; }

    public int AuthorId { get; set; }
    public Author Author { get; set; }
}
public class BookViewModel
{
    public int BookId { get; set; }

    public virtual List<AuthorViewModel> Authors { get; set; }    
}
public class AuthorViewModel
    {
        public int AuthorId { get; set; }

        public string AuthorName { get; set; }
    }
public class Library : DbContext
{
    public DbSet<Book> Books { get; set; }

    public DbSet<Author> Authors { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<BookAuthor>().HasKey(sc => new { sc.BookId, sc.AuthorId });
    }
}

Mapper:

 CreateMap<BookViewModel, Book>()
            .ForMember(b => b.BookId, opt => opt.MapFrom(b => b.BookId))
        .PreserveReferences()
        .ForMember(b => b.BookAuthors, opt => opt.MapFrom(b => b.Authors
        .Select(a => new BookAuthor { AuthorId = a.AuthorId })));

UPD: BookId jumps after creating a book(in steps of 1000).

After adding Include() to Find method, the error changed on

System.InvalidOperationException: 'The instance of entity type 'BookAuthor' cannot be tracked because another instance with the same key value for {'BookId', 'AuthorId'} is already being tracked. When attaching existing entities, ensure that only one entity instance with a given key value is attached. Consider using 'DbContextOptionsBuilder.EnableSensitiveDataLogging' to see the conflicting key values.'

Upvotes: 2

Views: 3690

Answers (2)

Vlad
Vlad

Reputation: 233

Finally it works! Below you can see the differences from original code.

P.S Perhaps you know better algorithm for Remove method, if so you can type it in comments then I will change the answer.

BookRepository:

 public Book Find(int id)
    {
        Book searchedBook = db.Books
          .Include(b => b.BookAuthors)
          .ThenInclude(b => b.Author)
          .SingleOrDefault(b => b.BookId == id);

        return searchedBook;
    }

BookService:

public void UpdateObject(BookViewModel viewModel)
        {
            Book parsedBook = Mapper.Map<BookViewModel, Book>(viewModel);
            //Book viewModelBook = parseBook;
            var book = db.Books.Find(viewModel.BookId);
            book.BookId = parsedBook.BookId;
            book.BookName = parsedBook.BookName;
            book.Genre = parsedBook.Genre;
            book.Pages = parsedBook.Pages;
            book.Publisher = parsedBook.Publisher;
            UpdateAssociatedObject(book, parsedBook);
        }
public void UpdateAssociatedObject(Book bookToUpdate, Book viewModelBook)
        {
            AddUpdatedAuthors(bookToUpdate, viewModelBook);
            RemoveUpdatedAuthors(bookToUpdate, viewModelBook);
            db.SaveChanges();
        }
     private void AddUpdatedAuthors(Book bookToUpdate, Book viewModelBook)
        {
            foreach (var authors in viewModelBook.BookAuthors)
            {
                var searchBookAuthor = bookToUpdate.BookAuthors.Find(b => b.AuthorId == authors.AuthorId);
                if (searchBookAuthor == null)
                {
                    bookToUpdate.BookAuthors.Add(authors);
                }
            }
        }
        private void RemoveUpdatedAuthors(Book bookToUpdate, Book viewModelBook)
        {
            int countOfAuthors = bookToUpdate.BookAuthors.Count;
            for (int i = 0; i < countOfAuthors; i++)
            {
                BookAuthor searchBookAuthor = null;
                foreach (var viewModelAuthors in viewModelBook.BookAuthors)
                {
                    if (viewModelAuthors.AuthorId == bookToUpdate.BookAuthors[i].AuthorId)
                    {
                        searchBookAuthor = viewModelAuthors;
                    }
                }
                if (searchBookAuthor == null)
                {
                    bookToUpdate.BookAuthors.Remove(bookToUpdate.BookAuthors[i]);
                    i--;
                    countOfAuthors--;
                }
            }
        }

Upvotes: 1

Anis ur Rehman
Anis ur Rehman

Reputation: 89

The problem looks with db.SaveChanges(). You are calling db.SaveChanges with same context multiple times in

db.SaveChanges();

UpdateAssociatedObject(book, viewModelBook);

and in UpdateAssociatedObject(book, viewModelBook); Try removing one db.SaveChanges().

Upvotes: 0

Related Questions