Many-to-many relationship update in EF Core

Question

In my two classes I created a many-to-many relationship:

public class Author
{
     [Key]
     [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
     public int Id { get; set; }
     [DisplayName("^First Name")]
     public required string FirstName { get; set; }
     [DisplayName("Last Name")]
     public required string LastName { get; set; }
     public ICollection<Book> Books { get; set; } = [];
}

public class Book : Product
{
    public required string ISBN { get; set; }
    
    [Range(1, 1000)]
    [Display(Name = "List Price")]
    public required double ListPrice { get; set; }
    
    [Range(1, 1000)]
    [Display(Name = "Price for 1-49")]
    public required double Price { get; set; }
   
    [Range(1, 1000)]
    [Display(Name = "Price for 50-99")]
    public required double Price50 { get; set; }
  
    [Range(1, 1000)]
    [Display(Name = "Price for 100+")]
    public required double Price100 { get; set; }
 
    [ValidateNever]
    public virtual required Category Category { get; set; }
 
    [ValidateNever]
    public virtual required ICollection<Author> Authors { get; set; } = [];
}

public class Product
{
     [Key]
     [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
     public int Id { get; set; }

     public required string Title { get; set; }

     public string Description { get; set; } = string.Empty;

     [AllowNull]
     public string? ImageUrl { get; set; } = "";
}

public class BookAuthor
{
      public required int BookId { get; set; }
      public required int AuthorId { get; set; }
}

And in DbContext, I configured the relationship like this:

modelBuilder.Entity<Author>() 
            .HasMany(ba => ba.Books)
            .WithMany(ba => ba.Authors)
            .UsingEntity<BookAuthor>();

My problem is when I want to update authors of books. It doesn't remove old data in table BookAuthor and only tries to insert new records for updated book. I don't want to remove records of relationship and reinsert them.

I use this code to update the books :

bookVM.book.Authors = new List<Author>();

foreach (int authorId in bookVM.AuthorsId)
{
     var author = _UnitOfWork.Author.Get(cat => cat.Id == authorId);
     bookVM.book.Authors.Add(author);
}

if (bookVM.book.Id == 0)
{
     _UnitOfWork.Book.Add(bookVM.book);
}
else
{
     _UnitOfWork.Book.Update(bookVM.book);
}

_UnitOfWork.Save();

Is there any way that EF Core can do it automatically?

Answer 1

Looking at the code provided I can see a few issues. The following looks like two red flags:

bookVM.book.Authors = new List<Author>();

Firstly, when it comes to EF entities, if you are going to use view models (recommended) never mix view models and entities. (bookVM.Book) The book VM should contain the data columns relevant to the Book entity where the entity is loaded from the DbContext when updating, or created & added if new.

Secondly, collection navigation properties in entities should never have public setters accessed. In the Book entity, the Authors navigation property should look like:

public virtual ICollection<Author> Authors { get; protected set; } = [];

You never want to see code outside of the entity doing something like book.Authors = ..... This is the primary culprit for duplicated data or extra references as EF is counting on the proxied Authors to track changes where references are added and removed. Using a setter breaks that tracking.

Next, your use of a unit of work wrapper will confuse things but you want to ensure that you are dealing with real, tracked references as much as possible and being very careful with detached data. I generally advise avoiding "Upsert" style methods, instead using separate explicit Insert and Update methods so you can better handle situations where a user expects to insert a book (that might already exist) or update a book (that doesn't exist). However, with an Upsert scenario, consider the following adjusted method (using DbContext):

// First we can fetch our Authors to use whether inserting or updating.
var authors = _context.Authors
    .Where(x => bookVM.AuthorIds.Contains(x.Id))
    .ToList();

// Consider validating whether authors.Count == bookVM.AuthorIds.Count in 
// case one or more Authors were not found...

if (bookVM.Id == 0)
{   // Insert
    Book book = new Book(bookVM.Id, bookVM.Title, authors, /* + Required (not null) properties */) 
    { 
         /* + optional properties... */
    };

    _context.Books.Add(book);
}
else
{ // Update
    var book = _context.Books
        .Include(x => x.Authors)
        .Single(x => x.Id == bookVM.Id);

    var existingAuthorIds = book.Authors.Select(x => x.Id).ToList();

    var authorIdsToAdd = bookVM.AuthorIds.Except(existingAuthorIds);
    var authorIdsToRemove = existingAuthorIds.Except(bookVM.AuthorIds);

    if (authorIdsToRemove.Any())
    {
        var authorsToRemove = book.Authors
            .Where(x => authorIdsToRemove.Contains(x.Id))
            .ToList();
        foreach(var author in authorsToRemove)
            book.Authors.Remove(author);
    }
    if (authorIdsToAdd.Any())
    {
        var authorsToAdd = authors
            .Where(x => authorIdsToAdd.Contains(x.Id))
            .ToList();
        foreach(var author in authorsToAdd)
            book.Authors.Add(author);
    }
}

_context.SaveChanges();

When updating associated entities in EF, fetch the entity from the database and eager load any associated data you might be updating. You need to be a bit more deliberate to identify what authors to add vs. remove rather than trying to replace a collection to reflect the current state. When you replace the collection, EF will be expecting that you you are simply adding all new associations and will not know that any association records need to be deleted.

When performing inserts you can initialize collections, ideally through a constructor since you do not want to expose a public setter on the collection reference. I recommend having a public constructor that takes all required properties and references, then leaving all optional, null-able ones set via their public setters. This helps ensure that an entity is always kept in a valid state prior to being persisted. When using an argument constructor on an entity, simply also include a protected default constructor for EF to use when creating entities read from the database.