I have an EF Core model called Image with a self-referencing many-to-many relationship through a property like:

public class Image {
    public int Id { get; set; }
    // ... other columns ...

    public List<Image> AlternativeImages { get; set; }
}

And my configuration is:

modelBuilder.Entity<Image>()
    .HasMany(i => i.AlternativeImages)
    .WithMany()
    .UsingEntity(j => j.ToTable("ImageAlternatives"));

Everything works when I add an Image with any amount of AlternativeImages, but when I update an Image with new AlternativeImages(If the already existing Image doesn't have Alternative images then there is no problem) EF tries to reinsert those old relationships in the join table, causing the error:

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes.
 ---> Npgsql.PostgresException (0x80004005): 23505: duplicate key value violates unique constraint "PK_ImageAlternatives"
DETAIL: Key ("AlternativeImagesId", "ImageId")=(..., ...) already exists.

ie: ImageA, ImageB and ImageC, If ImageA already has ImageB in its AlternativeImages, and I add ImageC to that list (and update mutual references to keep all relationships symmetrical), I get the exception saying that the (ImageAId, ImageBId) pair already exists even though both ImageA and ImageB are tracked as Unchanged, and only ImageC has state Added.. The ImageA and ImageB objects being used in the AlternativeImages lists are the exact same instances that are already being tracked by the context. They are not detached or recreated, EF is seeing the same references with EntityState.Unchanged.

Edit: The actual implementation is more complex (since Image belongs to another aggregate root in my domain model), here's a simplified version of the update operation that reproduces the same error:

public async Task UpdateAsync()
{
    using var dbContext = await _dbContextFactory.CreateDbContextAsync();
    using var transaction = await dbContext.Database.BeginTransactionAsync();
    
    try
    {
        var existingImage = await dbContext.Images
            .Include(i => i.AlternativeImages)
            .AsSplitQuery()
            .FirstOrDefaultAsync(s => s.Id == 10217);

        var tmp = new Image { ImageUrl = "..." };
        existingImage.AddAlternativeImage(tmp);

        foreach (var entry in dbContext.ChangeTracker.Entries<Image>())
        {
                Console.WriteLine("");
        }

        await dbContext.SaveChangesAsync();

        //await transaction.RollbackAsync();
        await transaction.CommitAsync();
    }
    catch
    {
        await transaction.RollbackAsync();
        throw;
    }
}

And this is the AddAlternativeImage method, it ensures bidirectional relationship consistency by adding the new image to all existing alternative images' lists while also adding those existing images to the new image's alternatives.

internal bool AddAlternativeImage(Image image)
{
    if (image == null)
        return false;

    bool addedSomething = false;

    // Initialize the collection if it is null.
    AlternativeImages ??= new List<Image>();

    // If the provided image is NOT the main one, try to add it.
    if (!image.ImageUrl.Equals(this.ImageUrl, StringComparison.OrdinalIgnoreCase))
    {
        if (!AlternativeImages.Any(existing => existing.ImageUrl.Equals(image.ImageUrl, StringComparison.OrdinalIgnoreCase)))
        {
            AlternativeImages.Add(image);
            addedSomething = true;
        }
    }

    // Process the alternative images of 'image' regardless of whether 'image' is the same as the main one.
    if (image.AlternativeImages != null)
    {
        foreach (var alt in image.AlternativeImages)
        {
            if (!alt.ImageUrl.Equals(this.ImageUrl, StringComparison.OrdinalIgnoreCase))
            {
                if (!AlternativeImages.Any(existing => existing.ImageUrl.Equals(alt.ImageUrl, StringComparison.OrdinalIgnoreCase)))
                {
                    AlternativeImages.Add(alt);
                    addedSomething = true;
                }
            }
        }
    }

    // Unified list of all alternative images.
    var unionImages = AlternativeImages.ToList();

    foreach (var alt in unionImages)
    {
        var newAlternatives = new List<Image>();
        alt.AlternativeImages ??= new List<Image>();

        // Include the main image.
        if (!this.ImageUrl.Equals(alt.ImageUrl, StringComparison.OrdinalIgnoreCase))
            newAlternatives.Add(this);

        // Add the other alternative images.
        foreach (var other in unionImages)
        {
            if (!other.ImageUrl.Equals(alt.ImageUrl, StringComparison.OrdinalIgnoreCase))
                newAlternatives.Add(other);
        }

        // Update on the same list to avoid problems with ef core tracking.
        if (alt.AlternativeImages == null)
            alt.AlternativeImages = newAlternatives;
        else
        {
            alt.AlternativeImages?.Clear();
            alt.AlternativeImages!.AddRange(newAlternatives);
        }

    }

    return addedSomething;
}

Edit 2: Sql definition of the join table

-- auto-generated definition
create table "ImageAlternatives"
(
    "AlternativeImagesId" integer not null
        constraint "FK_ImageAlternatives_Images_AlternativeImagesId"
            references "Images"
            on delete cascade,
    "ImageId"             integer not null
        constraint "FK_ImageAlternatives_Images_ImageId"
            references "Images"
            on delete cascade,
    constraint "PK_ImageAlternatives"
        primary key ("AlternativeImagesId", "ImageId")
);

I have an EF Core model called Image with a self-referencing many-to-many relationship through a property like:

public class Image {
    public int Id { get; set; }
    // ... other columns ...

    public List<Image> AlternativeImages { get; set; }
}

And my configuration is:

modelBuilder.Entity<Image>()
    .HasMany(i => i.AlternativeImages)
    .WithMany()
    .UsingEntity(j => j.ToTable("ImageAlternatives"));

Everything works when I add an Image with any amount of AlternativeImages, but when I update an Image with new AlternativeImages(If the already existing Image doesn't have Alternative images then there is no problem) EF tries to reinsert those old relationships in the join table, causing the error:

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes.
 ---> Npgsql.PostgresException (0x80004005): 23505: duplicate key value violates unique constraint "PK_ImageAlternatives"
DETAIL: Key ("AlternativeImagesId", "ImageId")=(..., ...) already exists.

ie: ImageA, ImageB and ImageC, If ImageA already has ImageB in its AlternativeImages, and I add ImageC to that list (and update mutual references to keep all relationships symmetrical), I get the exception saying that the (ImageAId, ImageBId) pair already exists even though both ImageA and ImageB are tracked as Unchanged, and only ImageC has state Added.. The ImageA and ImageB objects being used in the AlternativeImages lists are the exact same instances that are already being tracked by the context. They are not detached or recreated, EF is seeing the same references with EntityState.Unchanged.

Edit: The actual implementation is more complex (since Image belongs to another aggregate root in my domain model), here's a simplified version of the update operation that reproduces the same error:

public async Task UpdateAsync()
{
    using var dbContext = await _dbContextFactory.CreateDbContextAsync();
    using var transaction = await dbContext.Database.BeginTransactionAsync();
    
    try
    {
        var existingImage = await dbContext.Images
            .Include(i => i.AlternativeImages)
            .AsSplitQuery()
            .FirstOrDefaultAsync(s => s.Id == 10217);

        var tmp = new Image { ImageUrl = "..." };
        existingImage.AddAlternativeImage(tmp);

        foreach (var entry in dbContext.ChangeTracker.Entries<Image>())
        {
                Console.WriteLine("");
        }

        await dbContext.SaveChangesAsync();

        //await transaction.RollbackAsync();
        await transaction.CommitAsync();
    }
    catch
    {
        await transaction.RollbackAsync();
        throw;
    }
}

And this is the AddAlternativeImage method, it ensures bidirectional relationship consistency by adding the new image to all existing alternative images' lists while also adding those existing images to the new image's alternatives.

internal bool AddAlternativeImage(Image image)
{
    if (image == null)
        return false;

    bool addedSomething = false;

    // Initialize the collection if it is null.
    AlternativeImages ??= new List<Image>();

    // If the provided image is NOT the main one, try to add it.
    if (!image.ImageUrl.Equals(this.ImageUrl, StringComparison.OrdinalIgnoreCase))
    {
        if (!AlternativeImages.Any(existing => existing.ImageUrl.Equals(image.ImageUrl, StringComparison.OrdinalIgnoreCase)))
        {
            AlternativeImages.Add(image);
            addedSomething = true;
        }
    }

    // Process the alternative images of 'image' regardless of whether 'image' is the same as the main one.
    if (image.AlternativeImages != null)
    {
        foreach (var alt in image.AlternativeImages)
        {
            if (!alt.ImageUrl.Equals(this.ImageUrl, StringComparison.OrdinalIgnoreCase))
            {
                if (!AlternativeImages.Any(existing => existing.ImageUrl.Equals(alt.ImageUrl, StringComparison.OrdinalIgnoreCase)))
                {
                    AlternativeImages.Add(alt);
                    addedSomething = true;
                }
            }
        }
    }

    // Unified list of all alternative images.
    var unionImages = AlternativeImages.ToList();

    foreach (var alt in unionImages)
    {
        var newAlternatives = new List<Image>();
        alt.AlternativeImages ??= new List<Image>();

        // Include the main image.
        if (!this.ImageUrl.Equals(alt.ImageUrl, StringComparison.OrdinalIgnoreCase))
            newAlternatives.Add(this);

        // Add the other alternative images.
        foreach (var other in unionImages)
        {
            if (!other.ImageUrl.Equals(alt.ImageUrl, StringComparison.OrdinalIgnoreCase))
                newAlternatives.Add(other);
        }

        // Update on the same list to avoid problems with ef core tracking.
        if (alt.AlternativeImages == null)
            alt.AlternativeImages = newAlternatives;
        else
        {
            alt.AlternativeImages?.Clear();
            alt.AlternativeImages!.AddRange(newAlternatives);
        }

    }

    return addedSomething;
}

Edit 2: Sql definition of the join table

-- auto-generated definition
create table "ImageAlternatives"
(
    "AlternativeImagesId" integer not null
        constraint "FK_ImageAlternatives_Images_AlternativeImagesId"
            references "Images"
            on delete cascade,
    "ImageId"             integer not null
        constraint "FK_ImageAlternatives_Images_ImageId"
            references "Images"
            on delete cascade,
    constraint "PK_ImageAlternatives"
        primary key ("AlternativeImagesId", "ImageId")
);

• c#
• entity-framework-core

• "Everything works when I add an Image with any amount of AlternativeImages, but when I update an Image with new AlternativeImages" please show relevant code. "even though both ImageA and ImageB are tracked as Unchanged, and only ImageC has state Added.. The ImageA and ImageB objects being used in the AlternativeImages lists are the exact same instances that are already being tracked by the context. They are not detached or recreated, EF is seeing the same references with EntityState.Unchanged." please show evidence. – Charlieface Commented Mar 30 at 1:26
• I've updated the post with the relevant methods. After checking with ChangeTracker.Entries<Image>(), I can confirm all existing images show as Unchanged and only the new image is marked as Added. Anyways EF Core is trying to insert relationship entries that already exist in the database between these tracked as unchanged images, causing the duplicate key violation. – Alex Commented Mar 30 at 12:15
• Can you show the SQL definition of AlternativeImages join table definition? Do you really intend for the join table to have rows for both (Image1, Image2) and (Image2, Image1) Also is image.ImageUrl guaranteed unique? – Charlieface Commented Mar 31 at 11:31
• My initial idea was simply to save a record with pk = (id1, id2) where id1 < id2. This was just a first approach. However, even that isn’t working as expected. Steve Py’s suggested solution seems better overall, but I’d still like to understand why this original approach isn't working. @Charlieface – Alex Commented yesterday
• I have updated the post with the sql definition of the join table. – Alex Commented yesterday

1 Answer 1

Never expose/use a setter on navigation collections, and do not replace entities using Clear() + AddRange():

public class Image { public int Id { get; set; } // ... other columns ...

public ICollection<Image> AlternativeImages { get;  } = []; // Pre-initialize

}

When it comes to adjusting a collection of relations you want to ensure that the collection is eager loaded or explicitly loaded prior to modifying. (check) From there you need to explicitly do the work to identify which items should be removed and which should be added rather than trying to replace the entire set. In your case you have a set of peer images. Now these images will not have their collections loaded but the above will ensure their AlternativeImages collections are at least initialized. When we go to Add the image:

internal bool AddAlternativeImage(Image image)
{
    if (image == null)
        return false;

    bool addedSomething = false;

    // If the provided image is NOT the main one, try to add it.
    if (!image.ImageUrl.Equals(this.ImageUrl, StringComparison.OrdinalIgnoreCase))
    {
        if (!AlternativeImages.Any(existing => existing.ImageUrl.Equals(image.ImageUrl, StringComparison.OrdinalIgnoreCase)))
        {
            AlternativeImages.Add(image);
            addedSomething = true;
        }
    }

    // Process the alternative images of 'image' regardless of whether 'image' is the same as the main one.
    foreach (var alt in image.AlternativeImages)
    {
       if (!alt.ImageUrl.Equals(this.ImageUrl, StringComparison.OrdinalIgnoreCase))
       {
           // Associate this image as an alternate to the alternate
           alt.AlternativeImages.Add(this);
           if (!AlternativeImages.Any(existing => existing.ImageUrl.Equals(alt.ImageUrl, StringComparison.OrdinalIgnoreCase)))
           {
               AlternativeImages.Add(alt);
               addedSomething = true;
           }
       }
    }

    return addedSomething;
}

This might need some further adjustment but the crux of the issue is recomposing a list of alternate images and dealing with whether a collection is initialized or not. You want to avoid "setting" a collection as EF will interpret this as an instruction to re-insert rows which will result in duplicate insert errors. alt.AlternativeImages will not be pre-loaded when we iterate through image.AlternativeImages so worst case you might need to explicitly load them if you want to check each alternative if it already holds a reference to this image URI.

Storing a self referencing image structure like this will likely continued to be a pain to maintain vs. a simpler structure where image peers are simply sharing a FK to a parent entity. I.e.

var peerImages = await context.Images
    .Where(i => i.ParentId == parentId)
    .ToListAsync();