Entity Framework Core 8 : many-to-many relationships issue when updating the data

Question

I'm building an Entity Framework Core web project using code first migrations and when I update the data via update method, I get the following error message:

Violation of PRIMARY KEY constraint 'PK_ApplicationUserLanguages'. Cannot insert duplicate key in object 'dbo.ApplicationUserLanguages'. The duplicate key value is (29, a7314beb-fcbc-4e28-b331-6e2f5d3c44e8).

Here are my data structure:

public class Languages
{
     [Key]
     [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
     public int Id { get; set; }
     public string Name { get; set; }
     public Guid? UserId { get; set; }
     [JsonIgnore]
     public virtual List<ApplicationUser>? Users { get; set; }
}

public class ApplicationUser : IdentityUser
{
    public List<Languages>? Languages { get; set; }
}

Here's my DbContext:

public DbSet<Languages> Languages { get; set; }

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    modelBuilder.Entity<Languages>().HasMany(l => l.Users).WithMany(u => u.Languages);

modelBuilder.Entity<Languages>().HasData(
   new Languages { Id = 1, Name = "English" },
   new Languages { Id = 2, Name = "Spanish" },
   new Languages { Id = 3, Name = "French" },
   new Languages { Id = 4, Name = "German" },
   new Languages { Id = 5, Name = "Italian" },
   new Languages { Id = 6, Name = "Portuguese" },
   new Languages { Id = 7, Name = "Dutch" },
   new Languages { Id = 8, Name = "Russian" },
   new Languages { Id = 9, Name = "Chinese" },
   new Languages { Id = 10, Name = "Japanese" },
   new Languages { Id = 11, Name = "Korean" },
   new Languages { Id = 12, Name = "Arabic" },
   new Languages { Id = 13, Name = "Hindi" },
   new Languages { Id = 14, Name = "Bengali" },
   new Languages { Id = 15, Name = "Urdu" },
   new Languages { Id = 16, Name = "Punjabi" },
   new Languages { Id = 17, Name = "Telugu" },
   new Languages { Id = 18, Name = "Marathi" },
   new Languages { Id = 19, Name = "Tamil" },
   new Languages { Id = 20, Name = "Gujarati" },
   new Languages { Id = 21, Name = "Kannada" },
   new Languages { Id = 22, Name = "Odia" },
   new Languages { Id = 23, Name = "Malayalam" },
   new Languages { Id = 24, Name = "Sindhi" },
   new Languages { Id = 25, Name = "Assamese" },
   new Languages { Id = 26, Name = "Nepali" },
   new Languages { Id = 27, Name = "Sanskrit" },
   new Languages { Id = 28, Name = "Sindhi" });
}

Here's my code to update the database:

var user = await _context.Users.FirstOrDefaultAsync(u => u.Id == updateProfile.Id);

if (user != null)
{
    user.Languages = updateProfile.Languages;

    _context.Entry(user).State = EntityState.Modified;
    await _context.SaveChangesAsync();
}

I tried to execute code to delete the languages first then re add them but I keep getting the error. I'm not sure what I'm missing because I'm expecting Entity Framework Core to manage the foreign keys

Answer 1

The short answer is that you cannot update a reference list this way:

user.Languages = updateProfile.Languages;

When you want to update a one-to-many or many-to-many relationship where you have a set of existing relations, make changes to those relations, and want to save the updated relations, with EF it isn't as simple as replacing the collection.

For a start, to avoid the temptation or mistake of attempting this, for all collection navigation properties, remove any accessible setter. for instance in user:

public virtual ICollection<Language> Languages { get; } = []; // or new List<Language>();

This will immediately highlight problem operations in the code. If you have constructors initializing the collections, that is no longer necessary. If you have any code initializing or overwritting the collections, that needs to be removed.

Now to update the collection it helps to look at it from the database's perspective. While setting the collection to a new set might seem plausible by having the database delete all associated records and then insert the updated associations, the change tracking in EF doesn't work that way. Instead you need to tell EF specifically what items need to be removed and added, so that the proxy that enables tracking the collection can relay that information to build the SQL statements. If you "set" the collection on an entity, you blast any tracking capability on the collection.

var user = await _context.Users
    .Include(u => u.Languages)
    .FirstOrDefaultAsync(u => u.Id == updateProfile.Id);

if (user == null) return;

var existingLanguageIds = user.Languages.Select(l => l.Id).ToList();
var updatedLanguageIds = updateProfile.Languages.Select(l => l.Id).ToList();

var languageIdsToRemove = existingLanguageIds.Except(updatedLanguageIds);
var languageIdsToAdd = updatedLanguageIds.Except(existingLanguageIds);

if(languageIdsToRemove.Any())
{
    var languagesToRemove = user.Languages.Where(l => languageIdsToRemove.Contains(l.Id));
    foreach(var language in languagesToRemove)
        user.Languages.Remove(language);
}

if(languageIdsToAdd.Any())
{
    var languagesToAdd = await _context.Languages
        .Where(l => languageidsToAdd.Contains(l.Id))
        .ToListAsync();
    foreach(var language in languagesToAdd)
        user.Languages.Add(language);
}

await _context.SaveChangesAsync();

Basically we eager load the languages currently associated to the user and identify the Ids to add and remove, then adjust the tracked languages collection on the user. In this way we don't need to send the detached language entities with the updateProfile, we could just send the current updated set of languageIds. (Less data over the wire)

In passing detached entities you could argue to Attach() those instances to add and then associate them to the user instead of fetching them from the DbContext but fetching them asserts that all passed languages are actual current records before attempting to associate them, and before attaching an entity to a DbContext you should always check the local tracking cache on the DbContext to ensure it isn't already tracking an instance, otherwise you get another nasty situational runtime exception.