EF core tries to add a duplicated row to join table during entity update

Question

I've researched a ton and couldn't find a solution that works for me. The issue I have is the following:

I have a many to many relationship among my entities and I have a join table (without a "join model" in the code). And when trying to update an entity, EF Core tries to add a row to the join table, which already exists.

The thing is, that one of the tables is constant & is populated during migration and shouldn't be ever modified. The question is, how to tell EF Core, to not add any existing, valid rows to the second table & to the join table?

Here's the code - relationship configuration:

public class FirstModelsMap : IEntityTypeConfiguration<FirstModel>
{
    public void Configure(EntityTypeBuilder<FirstModel> builder)
    {
        builder.HasKey(p => p.Id);

        builder.HasIndex(nameof(FirstModel.InternalGuid));

        builder.Property(p => p.Secret)
            .HasMaxLength(128)
            .IsRequired();

        builder.Property(p => p.Name)
            .IsRequired();

        builder.Property(p => p.Url)
            .IsRequired();

        builder.Property(p => p.InternalGuid)
            .IsRequired();

        builder.HasMany(p => p.SecondModels)
            .WithMany(p => p.FirstModels)
            .UsingEntity(p => p.ToTable("FirstModelSecondModels"));
    }
}

First entity:

public class FirstModel
{
    public long Id { get; set; }
    public Guid InternalGuid { get; set; }
    public string Name { get; set; }
    public Uri Url { get; set; }
    public string Secret { get; set; }

    public ICollection<SecondModel> SecondModels { get; set; }
}

Second entity:

public class SecondModel
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public string InternalName { get; set; }

    public ICollection<FirstModel> FirstModels { get; set; }
}

What I try to do in the repository:

    public void Update(FirstModel firstModel)
    {
        firstModel.SecondModels = database.SecondModels.Where(e => firstModel.SecondModels.Select(e => e.Id).Contains(e.Id)).ToList();
        // Tried adding this row, based on some StackOverflow answers
        database.SecondModels.AttachRange(firstModel.SecondModels);
        database.Update(firstModel);
    }

What I get as a result when calling SaveChanges() in the service:

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

Inner Exception:SqlException: Violation of PRIMARY KEY constraint 'PK_FirstModelsSecondModels'. Cannot insert duplicate key in object 'dbo.FirstModelsSecondModels'. The duplicate key value is (1, 1).

My SecondModels table is being populated with migration & is not expected to be modified. The join table may be modified. However I want to let EF Core know, that some rows may already be in place. I want the join table to adjust based on the public ICollection<SecondModel> SecondModels { get; set; } value of the FirstModel. So that not relevant rows are deleted, existing rows are not added, new rows are added.

I'm sure I'm doing something wrong here (otherwise it would work). Can you please help?

Answer 1

I've ended up modifying the method the following way, which made everything work as expected, although I don't think this is the best solution by any means:

 public async Task<bool> UpdateAsync(FirstModel firstModel)
    {
        var secondModelIds = firstModel.SecondModels.Select(e => e.Id);
        var existingFirstModel  = await database.FirstModel .Include(w => w.SecondModels).FirstOrDefaultAsync(w => firstModel.Id == w.Id);
        if (existingFirstModel  is null)
        {
            return false;
        }

        existingFirstModel.SecondModels= database.SecondModels.Where(e => secondModelIds .Contains(e.Id)).ToList();
        existingFirstModel.InternalGuid = firstModel.InternalGuid;
        existingFirstModel.Secret = firstModel.Secret;
        existingFirstModel.Url = firstModel.Url;
        existingFirstModel.Name = firstModel.Name;
        
        // This is called in the service layer
        await database.SaveChangesAsync()

        return true;
    }