Many-to-Many does not get updated for replaced entity with shared primary key

[Dresel]

We have the following use case:

• EntityA has a one to one relationship with EntityB. The PK of EntityB is also the FK of EntityA
• EntityB has a many to many relationship with EntityC
• We are replacing EntityB for an existing EntityA
• The many to many relationship does not get updated

// <Nullable>Enable</Nullable>

public static void Main(string[] args)
{
    ServiceCollection services = new ServiceCollection();
    services.AddDbContext<MyContext>(options =>
    {
        options.UseSqlite("DataSource=local.db");
        options.UseLazyLoadingProxies();
    });

    var buildServiceProvider = services.BuildServiceProvider();

    using (var scope = buildServiceProvider.CreateScope())
    {
        var context = scope.ServiceProvider.GetRequiredService<MyContext>();
        context.Database.EnsureDeleted();
        context.Database.EnsureCreated();
    }

    using (var scope = buildServiceProvider.CreateScope())
    {
        var context = scope.ServiceProvider.GetRequiredService<MyContext>();
        context.Add(new EntityC());
        context.Add(new EntityC());

        context.SaveChanges();
    }

    using (var scope = buildServiceProvider.CreateScope())
    {
        var context = scope.ServiceProvider.GetRequiredService<MyContext>();
        context.Add(new EntityA()
        {
            EntityB = new EntityB()
            {
                EntitiesC = { context.EntitiesC.Find(1) },
            }
        });

        context.SaveChanges();
    }

    using (var scope = buildServiceProvider.CreateScope())
    {
        var context = scope.ServiceProvider.GetRequiredService<MyContext>();
        var entityA = context
            .EntitiesA
            .Include(x => x.EntityB)
            //.ThenInclude(x => x.EntitiesC) // if included, EntitiesC will be empty
            .First();

        var entityC = context.EntitiesC.Find(2);
        Console.WriteLine($"Setting EntityC with ID {entityC.Id}");

        entityA.EntityB = new EntityB()
        {
            EntitiesC = { entityC }
        };

        // This would work
        //entityA.EntityB.EntitiesC = new[] {entityC};

        context.SaveChanges();
    }

    using (var scope = buildServiceProvider.CreateScope())
    {
        var context = scope.ServiceProvider.GetRequiredService<MyContext>();
        
        Console.WriteLine($"EntityC has ID {(context.EntitiesA.First().EntityB.EntitiesC.FirstOrDefault()?.Id.ToString() ?? "null")}");
    }
}

This produces the following output:

Setting EntityC with ID 2
EntityC has ID 1

If we uncomment and eager load EntitiesC then it will be empty:

Setting EntityC with ID 2
EntityC has ID null

Normal navigation properties do get updated (e.g. one to many). If we use separate primary key and foreign key for EntityB it would also work.

Include provider and version information

EF Core version:
Database provider: Microsoft.EntityFrameworkCore.Sqlite 5.0.3
Target framework: net5.0

[ajcvickers]

@AndriySvyryd After investigating, I think this is an update pipeline issue. Here's what I think is happening. First, looking at the case that works where the EntityB has a non-PK FK to EntityA:

public class EntityB
{
    public int Id { get; set; }
    public int EntityAId { get; set; }
    public virtual EntityA EntityA { get; set; }
    public virtual ICollection<EntityC> EntitiesC { get; } = new List<EntityC>();
}

Looking at the change tracker before the final SaveChanges shows this:

EntityA {Id: 1} Unchanged
  Id: 1 PK
  EntityB: {Id: -2147482646}
EntityB {Id: -2147482646} Added
  Id: -2147482646 PK Temporary
  EntityAId: 1 FK
  EntityA: {Id: 1}
  EntitiesC: [{Id: 2}]
EntityB {Id: 1} Deleted
  Id: 1 PK
  EntityAId: 1 FK
  EntityA: <null>
  EntitiesC: []
EntityBEntityC (Dictionary<string, object>) {EntitiesBId: -2147482646, EntitiesCId: 2} Added
  EntitiesBId: -2147482646 PK FK Temporary
  EntitiesCId: 2 PK FK
EntityC {Id: 2} Unchanged
  Id: 2 PK
  EntitiesB: [{Id: -2147482646}]

The update pipeline generates the following changes:

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (3ms) [Parameters=[@p0='1'], CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      DELETE FROM [EntitiesB]
      WHERE [Id] = @p0;
      SELECT @@ROWCOUNT;
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[@p1='1'], CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      INSERT INTO [EntitiesB] ([EntityAId])
      VALUES (@p1);
      SELECT [Id]
      FROM [EntitiesB]
      WHERE @@ROWCOUNT = 1 AND [Id] = scope_identity();
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[@p2='2', @p3='2'], CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      INSERT INTO [EntityBEntityC] ([EntitiesBId], [EntitiesCId])
      VALUES (@p2, @p3);

Now, if we remove the non-PK FK and use the PK as the FK to EntityA, which is the case that fails:

public class EntityB
{
    public int Id { get; set; }
    public virtual EntityA EntityA { get; set; }
    public virtual ICollection<EntityC> EntitiesC { get; } = new List<EntityC>();
}

In this case the change tracker looks like this:

EntityA {Id: 1} Unchanged
  Id: 1 PK
  EntityB: {Id: 1}
EntityB (Shared) {Id: 1} Added
  Id: 1 PK FK
  EntityA: {Id: 1}
  EntitiesC: [{Id: 2}]
EntityB (Shared) {Id: 1} Deleted
  Id: 1 PK FK
  EntityA: <null>
  EntitiesC: []
EntityBEntityC (Dictionary<string, object>) {EntitiesBId: 1, EntitiesCId: 2} Added
  EntitiesBId: 1 PK FK
  EntitiesCId: 2 PK FK
EntityC {Id: 2} Unchanged
  Id: 2 PK
  EntitiesB: [{Id: 1}]

This looks correct; in particular, the added join table entry is present.

However, SaveChanges is a no-op. I'm guessing this is because the update pipeline is collapsing the reparenting. However, in doing this we also fail to insert into the join table.

Full runnable repro:

public class EntityA
{
    public int Id { get; set; }
    public virtual EntityB EntityB { get; set; }
}

public class EntityB
{
    public int Id { get; set; }
    //public int EntityAId { get; set; }
    public virtual EntityA EntityA { get; set; }
    public virtual ICollection<EntityC> EntitiesC { get; } = new List<EntityC>();
}

public class EntityC
{
    public int Id { get; set; }
    public virtual ICollection<EntityB> EntitiesB { get; } = new List<EntityB>();
}

public class SomeDbContext : DbContext
{
    private static ILoggerFactory ContextLoggerFactory
        => LoggerFactory.Create(b => b.AddConsole().SetMinimumLevel(LogLevel.Information));

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(Your.ConnectionString)
            .UseLoggerFactory(ContextLoggerFactory)
            .EnableSensitiveDataLogging();

    public DbSet<EntityA> EntitiesA { get; set; }
    public DbSet<EntityB> EntitiesB { get; set; }
    public DbSet<EntityC> EntitiesC { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder
            .Entity<EntityA>()
            .HasOne(e => e.EntityB)
            .WithOne(e => e.EntityA)
            .HasForeignKey<EntityB>(e => e.Id);
    }
}

public class Program
{
    public static void Main()
    {
        using (var context = new SomeDbContext())
        {
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            context.Add(new EntityC());
            context.Add(new EntityC());

            context.SaveChanges();
        }

        using (var context = new SomeDbContext())
        {
            context.Add(new EntityA()
            {
                EntityB = new EntityB()
                {
                    EntitiesC = { context.EntitiesC.Find(1) },
                }
            });

            context.SaveChanges();
        }

        using (var context = new SomeDbContext())
        {
            var entityA = context.EntitiesA.Include(x => x.EntityB).First();
            var entityC = context.EntitiesC.Find(2);

            entityA.EntityB = new EntityB()
            {
                EntitiesC = { entityC }
            };

            context.ChangeTracker.DetectChanges();

            context.SaveChanges();
        }
    }
}