Getting duplicate key error on foreign key change

Question

I've got an issue with updating a foreign key value to a new id. I want to update my contact person id but it gives me a duplicate key error.

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.

MySqlConnector.MySqlException (0x80004005): Duplicate entry '1f1cd1d9-25a1-4dee-bbd4-2b79a80551dd' for key 'branches.IX_Branches_ContactPersonId'

The user with the specified id does exist but I'm confused why it seems like its creating another record.

The relation is a one to one: A branch should have a contactperson but a user is not always a contactperson. To specify this relation a branch has a ContactPersonId and ContactPerson referring to the Id of a User.

Branch model

public class Branch
{
    public Guid Id { get; set; } = Guid.NewGuid()!;
    public string BranchName { get; set; } = default!;
    public Guid? ContactPersonId { get; set; } = default!;
    public User? ContactPerson { get; set; } = default!;

    public DateTime Created { get; set; } = DateTime.Now;
  
    public ICollection<User>? Users { get; set;  } = new List<User>();
}

User model

public class User
{
    public Guid Id { get; set; } = Guid.NewGuid()!;
    public string FullName { get; set; } = default!;
    public string Email { get; set; } = default!;
    public string PhoneNumber { get; set; } = default!;
    public string? Password { get; set; }
    public string? Salt { get; set; }
    public UserRoles Role { get; set; }
    public Guid? BranchId { get; set; }
}

To define the relations I added this to my context

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Branch>()
        .HasOne<User>(s => s.ContactPerson)
        .WithOne()
        .HasForeignKey<Branch>(s => s.ContactPersonId);

    modelBuilder.Entity<Branch>()
        .HasMany<User>(s => s.Users)
        .WithOne()
        .HasForeignKey("BranchId");
}

Updating in the service:

public async Task<bool> UpdateAsync(Branch item)
{
    var result = await _dbContext.Branches.FirstAsync(x => x.Id == item.Id);
    
    if (result != null)
    {
        result.BranchName = item.BranchName;
        result.Street = item.Street;
        result.HouseNumber = item.HouseNumber;
        result.Addition = item.Addition;
        result.City = item.City;
        result.ContactPersonId = item.ContactPersonId;
        result.BasicRiskFactor = item.BasicRiskFactor;
        result.DisplayDashboard = item.DisplayDashboard;
        result.RequestAssessment = item.RequestAssessment;
        result.LicenceDate = item.LicenceDate;
    }
    
    await _dbContext.SaveChangesAsync();

    return true;
}

Answer 1

The relation is a one to one: A branch should have a contactperson but a user is not always a contactperson.

Yes, that is exactly what is done by modelBuilder.Entity<Branch>().HasOne<User>(s => s.ContactPerson).WithOne(), but it means that User can be a contact person only for single branch, to enforce that EF Core creates IX_Branch_ContactPersonId index, which you are hitting. It seems that you are trying to set user which is already a contact person as as contact person for another branch. You need either remove the user from being a contact person from that other branch or use one-to-many relationship (or select a user which is not assigned as contact person for another branch).