ChangeDetector considers a JoinEntity as deleted even if you add that back

[velmohan]

When a many to many relationship is implemented using SkipNavigations, we have noticed that ChangeDetector does not detect when a removed JoinEntity is added again. What I have given below is not the exact code we use but is a reduced version that has the required minimal steps to reproduce the issue.

At a high level, here are the steps to reproduce the issue.

Step 1: Add a couple of entities via skip navigations and save to the DB
Step 2. Remove one of them from the SkipNavigation Collection.
Step 3. Run ChangeDetector.DetecChanges()
Step 4. Add the entity back
Step 5. Saving changes now deletes the entity you removed at Step 2 even though you added it back at Step 4.

  // Step 1: Add a Post with 2 tags using the SkipNavigation property `post.Tags`
   var post = context.Posts.Single(e => e.Id == 2);
   var tag1 = context.Tags.Single(e => e.Id == 1);
   var tag2 = context.Tags.Single(e => e.Id == 2);

   post.Tags.Add(tag1);  
   post.Tags.Add(tag2);

   context.ChangeTracker.DetectChanges();
   Console.WriteLine(context.ChangeTracker.DebugView.LongView);

   context.SaveChanges();

   var updatedPost = context.Posts.Single(e => e.Id == 2);

   foreach (var t in updatedPost.Tags)
   {
       Console.WriteLine($"After First Update Post {updatedPost.Id}----Tag {t.Id}");
   }

 // Step 2:  Remove a tag
   post = context.Posts.Single(e => e.Id == 2);
   var firstTag = post.Tags.First();
   post.Tags.Remove(firstTag);

  // Step 3: Run DetectChanges (this step is necessary for reproducing the issue)
   context.ChangeTracker.DetectChanges();
   Console.WriteLine(context.ChangeTracker.DebugView.LongView);

   // Step 4: Add the deleted tag again
   post.Tags.Add(firstTag);

   context.ChangeTracker.DetectChanges();
   Console.WriteLine(context.ChangeTracker.DebugView.LongView);
   // The output shows the firstTag still has Deleted status

  //  Step 5: Save the changes
   context.SaveChanges();

   updatedPost = context.Posts.Single(e => e.Id == 2);
 // Step 6: The tag deleted in Step 4 has been deleted from the DB
   foreach (var t in updatedPost.Tags)
   {
       Console.WriteLine($"After Second Update Post {updatedPost.Id}----Tag {t.Id}");
   }```

### Output
info: 28/04/2024 06:00:14.912 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT "p"."Id", "p"."BlogId", "p"."Content", "p"."Title"
      FROM "Posts" AS "p"
      WHERE "p"."Id" = 2
      LIMIT 2
info: 28/04/2024 06:00:14.932 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT "t"."Id", "t"."Text"
      FROM "Tags" AS "t"
      WHERE "t"."Id" = 1
      LIMIT 2
info: 28/04/2024 06:00:14.935 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT "t"."Id", "t"."Text"
      FROM "Tags" AS "t"
      WHERE "t"."Id" = 2
      LIMIT 2
Post {Id: 2} Unchanged
    Id: 2 PK
    BlogId: 1 FK
    Content: 'F# 5 is the latest version of F#, the functional programming...'
    Title: 'Announcing F# 5'
  Blog: <null>
  Tags: [{Id: 1}, {Id: 2}]
PostTag (Dictionary<string, object>) {PostsId: 2, TagsId: 1} Added
    PostsId: 2 PK FK
    TagsId: 1 PK FK
PostTag (Dictionary<string, object>) {PostsId: 2, TagsId: 2} Added
    PostsId: 2 PK FK
    TagsId: 2 PK FK
Tag {Id: 1} Unchanged
    Id: 1 PK
    Text: '.NET'
  Posts: [{Id: 2}]
Tag {Id: 2} Unchanged
    Id: 2 PK
    Text: 'Visual Studio'
  Posts: [{Id: 2}]

info: 28/04/2024 06:00:14.966 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (0ms) [Parameters=[@p0='2', @p1='1'], CommandType='Text', CommandTimeout='30']
      INSERT INTO "PostTag" ("PostsId", "TagsId")
      VALUES (@p0, @p1);
info: 28/04/2024 06:00:14.967 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (0ms) [Parameters=[@p0='2', @p1='2'], CommandType='Text', CommandTimeout='30']
      INSERT INTO "PostTag" ("PostsId", "TagsId")
      VALUES (@p0, @p1);
info: 28/04/2024 06:00:14.973 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT "p"."Id", "p"."BlogId", "p"."Content", "p"."Title"
      FROM "Posts" AS "p"
      WHERE "p"."Id" = 2
      LIMIT 2
After First Update Post 2----Tag 1
After First Update Post 2----Tag 2
info: 28/04/2024 06:00:14.974 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT "p"."Id", "p"."BlogId", "p"."Content", "p"."Title"
      FROM "Posts" AS "p"
      WHERE "p"."Id" = 2
      LIMIT 2
Post {Id: 2} Unchanged
    Id: 2 PK
    BlogId: 1 FK
    Content: 'F# 5 is the latest version of F#, the functional programming...'
    Title: 'Announcing F# 5'
  Blog: <null>
  Tags: [{Id: 2}]
PostTag (Dictionary<string, object>) {PostsId: 2, TagsId: 1} Deleted
    PostsId: 2 PK FK
    TagsId: 1 PK FK
PostTag (Dictionary<string, object>) {PostsId: 2, TagsId: 2} Unchanged
    PostsId: 2 PK FK
    TagsId: 2 PK FK
Tag {Id: 1} Unchanged
    Id: 1 PK
    Text: '.NET'
  Posts: []
Tag {Id: 2} Unchanged
    Id: 2 PK
    Text: 'Visual Studio'
  Posts: [{Id: 2}]

Post {Id: 2} Unchanged
    Id: 2 PK
    BlogId: 1 FK
    Content: 'F# 5 is the latest version of F#, the functional programming...'
    Title: 'Announcing F# 5'
  Blog: <null>
  Tags: [{Id: 2}, {Id: 1}]
PostTag (Dictionary<string, object>) {PostsId: 2, TagsId: 1} Deleted
    PostsId: 2 PK FK
    TagsId: 1 PK FK
PostTag (Dictionary<string, object>) {PostsId: 2, TagsId: 2} Unchanged
    PostsId: 2 PK FK
    TagsId: 2 PK FK
Tag {Id: 1} Unchanged
    Id: 1 PK
    Text: '.NET'
  Posts: [{Id: 2}]
Tag {Id: 2} Unchanged
    Id: 2 PK
    Text: 'Visual Studio'
  Posts: [{Id: 2}]

info: 28/04/2024 06:00:14.984 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (0ms) [Parameters=[@p0='2', @p1='1'], CommandType='Text', CommandTimeout='30']
      DELETE FROM "PostTag"
      WHERE "PostsId" = @p0 AND "TagsId" = @p1
      RETURNING 1;
info: 28/04/2024 06:00:14.990 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT "p"."Id", "p"."BlogId", "p"."Content", "p"."Title"
      FROM "Posts" AS "p"
      WHERE "p"."Id" = 2
      LIMIT 2
After Second Update Post 2----Tag 2

### Include provider and version information
EF Core version: 8.0.4
Database provider: Microsoft.EntityFrameworkCore.Sqlite
Target framework: NET 8.0
Operating system: Windows
IDE: Visual Studio 2022 17.9.4

[ajcvickers]

Looks like same root cause as #31631.

[Webreaper]

Thanks @ajcvickers. Curious - you've closed this as "not planned", is that just because this is a dupe, and it'll be fixed under the other issue? Or are you not planning to fix for either issue?

[ajcvickers]

@Webreaper It's just because this is a dupe. I definitely intend to address issues in this area. Prioritization is tricky at the moment, so bare with us.

[Webreaper]

Awesome, thought that was it - just wanted to check. We have a workaround for this though, so we're okay for now.

Context - @velmohan works in my team. :)