Update not working since switch to DbContextFactory

I recently switched to a ContextFactory because of second operation was started on this context ...

So I registered DbContextFactory:

builder.Services.AddDbContextFactory<CharacterSheetDbContext>(
    options => {
        options.UseMySql(
            builder.Configuration.GetConnectionString("DefaultConnection"),
            new MySqlServerVersion(new Version(8, 0, 27))
        );
        options.EnableSensitiveDataLogging();
    }
);

Then I have my Service layer:

with factory injected in the ctor:

public ARepository(IDbContextFactory<CharacterSheetDbContext> contextFactory) {
        _contextFactory = contextFactory;
    }

and Update method (where I create my context):

public async Task UpdateAsync(TEntity entity) {
        await using var context = await _contextFactory.CreateDbContextAsync();
        var set = context.Set<TEntity>();
        //_context.ChangeTracker.Clear(); <-- (tried with and without this one)
        set.Update(entity);
        await context.SaveChangesAsync();
    }

but when I call Update on an entity it throws me the following error:

An exception occurred in the database while saving changes for context type 'Model.Configurations.CharacterSheetDbContext'.
      Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.
       ---> MySqlConnector.MySqlException (0x80004005): Duplicate entry '3-9' for key 'characters_has_personalities.PRIMARY'

So it tries to insert this stuff again but I just want it to be not updated if not changed.

What should I do know? Thank you in advance!

The reason you are getting that exception since switching to a DbContext factory is because you are taking an entity and telling a completely new DbContext instance created by the factory to persist it, and any entities associated with it.

The issue you are seeing most certainly isn't with the entity you are trying to update, but child, or referenced entities that entity has. In your case if you are trying to persist a Character, and that Character has a collection of Personalities, then since that DBContext isn't tracking those instances of Personalities, it will treat them as if they are new entities and try and insert them.

Working with detached entities is frankly a pain. It might look like a simple matter of passing an entity to a new DbContext instance and calling Update, but aside from the simplest scenarios it is rarely that simple. Trying to build a data access layer as a Generic implementation just adds to the complexity.

At the most basic level, when processing an entity that has relations you need to associate those related entities to the DbContext when persisting. However, first you need to check that the DbContext instance in question isn't already tracking a matching entity and substitute the reference if it is.

public async Task UpdateCharacterAsync(Character character) 
{
    await using var context = await _contextFactory.CreateDbContextAsync();
    var set = context.Set<Character>();

    foreach(var personality in character.Personalities)
    {
        context.Attach(personality);
    }
    set.Update(entity);
    await context.SaveChangesAsync();
}

That at a bare minimum might get you moving in the right direction. Because we need to know about Personalities any anything else a Character might be referencing and ensure the DbContext is tracking those, this type of method cannot really be Generic without resorting to a lot of complexity and reflection. Even then, this approach is not fool-proof, nor is it efficient. If it is ever possible for the same related entity to appear more than once in a set of relationships, and the passed in references to that entity are not the same reference, you can encounter errors when trying to attach the entity for the second time. This is especially important when dealing with deserialized entitiy graphs such as with MVC or Ajax calls where the entity and related data is deserialized from JSON. For instance if the Character and Personality have a reference to a CreatedBy User entity, those would need to be attached. Say you have 2 references to User ID #1, where they are 2 distinct objects rather than 2 references to the same object. When you attach the first instance, everything is fine, but attempting to attach the second instance would result in an error that the Context is already tracking an instance with the same ID.

Working with detached entities can be a pain. Working with detached entity graphs (relationships) easily becomes a nightmare as errors like this are situational.

It also isn't efficient to use Update as this will result in an UPDATE statement updating all columns in the table whether something changed or not. This also generates situations where you need to consider stale data overwrites if you aren't using things like Snapshots and Row Versioning. While it might seem better to pass an entity and just call Update to avoid re-loading an entity, this has several drawbacks that will only be revealed somewhat vaguely when SaveChanges() is called. My recommendation when it comes to performing updates is to use a read and copy across approach as this allows you to validate the data state as you go, and it ensures that DB UPDATE statements only run if values change, and for what columns actually change.

Ideally this is coupled with sending DTOs to the Update call which only contain the fields that could possibly be changed, and in the case of references, only need to pass IDs or collections of IDs for those references. This keeps the payload size from client to server as compact as possible rather than passing around entire entity structures.

Automapper's Map(source, destination) method can help copy updated values from the DTO to the loaded entity, and EF's change tracking takes over from there to decide if an UPDATE statement actually needs to execute.

The method ends up looking more like:

public async Task UpdateAsync(UpdateCharacterDTO characterDto) 
{
    if (characterDto == null) throw new ArgumentNullException("characterDto");

    await using var context = await _contextFactory.CreateDbContextAsync();
    var character = context.Characters
        .Include(x => x.Personalities)
        .Single(x => x.CharacterId == characterDTO.CharacterId);

    // Here you could check a row version on the DB vs. DTO to see if the DB had changed since the data used to build the DTO was read.

    _mapper.Map(characterDto, character);

    //If personalities can be added or removed...
    var existingPersonalityIds = character.Personalities.Select(x => x.PersonalityId);
    var personalityIdsToAdd = characterDto.PersonalityIds.Except(existingPersonalityIds).ToList();
    var personalityIdsToRemove = existingPersonalityIds.Except(characterDto.PersonalityIds).ToList();

    var personalitiesToAdd = await context.Personalities.Where(x => personalityIdsToAdd.Contains(x.PersonalityId).ToListAsync();
    var personalitiesToRemove = character.Personalities.Where(x => personalityIdsToRemove.Contains(x.PersonalityId)).ToList();

    foreach(var personality in personalitiesToRemove)
        character.Personalities.Remove(personality);

    foreach(var personality in personalitiesToAdd)
        character.Personalities.Add(personality);

    await context.SaveChangesAsync();
}

Where CharacterDTO is a data container containing the ID and fields that can possibly be updated, as well as a collection of IDs for the personalities. (which a user may have added or removed items)

It results in a bit more code, but it should be fairly easy to follow what it is doing and there is no confusion about what EF is expected to be tracking/referencing. Ideally you can avoid a lot of that complexity by making operations more atomic, such as that there would be simpler calls to Add and Remove personalities and other related elements rather than trying to update an entire object graph in one top-level Update method. Update methods like that work well enough, but only so long as the entities involved are tracked by the same DbContext instance from start to finish.