Entity Framework Core tries to add old entry when adding new on Many-to-Many

Question

I have a many-to-many relationship in my EF Core 5.0.2 code-first:

public sealed class BlipPluginDto
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public string BlipPluginId { get; set; }

    [Required]
    public ICollection<SmartContactDto> SmartContacts { get; set; }
}

public sealed class SmartContactDto
{
    [Key]
    public int SmartContactId { get; set; }

    [Required]
    public string Identifier { get; set; }

    public ICollection<BlipPluginDto> BlipPlugins { get; set; }
}

Relationship is configured in the DBContext(MarketplaceDb) as:

modelBuilder.Entity<BlipPluginDto>().HasMany(bp => bp.SmartContacts).WithMany(sc => sc.BlipPlugins);

Then I try to add a record of SmartContactDto(new or existing) to a existing BlipPluginDto:

public async Task<SmartContactDto> GetOrCreateSmartContactAsync(
    string identifier,
    CancellationToken cancellationToken) =>
        await MarketplaceDb.SmartContacts
            .Include(sc => sc.BlipPlugins)
            .FirstOrDefaultAsync(sc => sc.Identifier == identifier, cancellationToken)
        ?? new SmartContactDto() { Identifier = identifier };

public async Task<bool> AddActivePluginAsync(
    string smartContactIdentifier,
    string blipPluginId,
    CancellationToken cancellationToken = default)
{
    var blipPlugin = await _blipPluginService.GetPluginByIdAsync(blipPluginId, cancellationToken);
    if (blipPlugin == default)
    {
        return false;
    }

    var smartContact = await GetOrCreateSmartContactAsync(smartContactIdentifier, cancellationToken);

    if (smartContact.BlipPlugins?.Any(bp => bp.BlipPluginId == blipPluginId) == true)
    {
        return false;
    }

    blipPlugin.SmartContacts.Add(smartContact);

    MarketplaceDb.Update(blipPlugin);

    await MarketplaceDb.SaveChangesAsync(cancellationToken);
    return true;
}

The _blipPluginService.GetPluginByIdAsync:

public async Task<BlipPluginDto> GetPluginByIdAsync(string id, CancellationToken cancellationToken = default)
{
    var plugin = await MarketplaceDb.BlipPlugins
        .Include(bp => bp.SmartContacts)
        .FirstOrDefaultAsync(bp => bp.BlipPluginId == id, cancellationToken);
    return plugin;
}

If the blipPlugin doesn't have any smartContact it works fine (the first insert of a SmartContactDto), but when the blipPlugin already have one smartContact entity is telling me that I'm trying to add a item with the same id on the Join table (that was created by EF Core).

Assuming that I'm trying to add a SmartContactDto with SmartContactId = 68 to a BlipPluginDto with BlipPluginId = "foo" that already have one SmartContactDto with SmartContactId = 12 I'm getting the following Inner Exception:

Violation of PRIMARY KEY constraint 'PK_BlipPluginDtoSmartContactDto'. Cannot insert duplicate key in object 'Marketplace.BlipPluginDtoSmartContactDto'. The duplicate key value is (foo, 12)

I've tried many different ways but I'm always stuck at this, even if I do the opposite (create and save a new smartContact then add the blipPlugin to the smartContact).

Answer 1

All of this work needs to be done within the same MarketplaceDb Context (unit of work).

I suspect that your GetOrCreateSmartContact is using a different DbContext to that where you retrieve the original Plugin in the _blipPlugInService, which you then update at the end.

Therefore, when you add the existing SmartContact that is found by GetOrCreateSmartContact it has come from a different context to that where you are performing the final

blipPlugin.SmartContacts.Add(smartContact);

MarketplaceDb.Update(blipPlugin);

await MarketplaceDb.SaveChangesAsync(cancellationToken);

As a result, this final context is not tracking the SmartContact you retrieved so thinks its a new one.

In summary, when perform a batch of actions such as this you must use the same instance of the DbContext for Entity Framework to do its magic.