The change of a navigation to a new instance and removing the old instance throws an Exception

[JakobFerdinand]

Entities and DbContext

sealed class MyDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        base.OnConfiguring(optionsBuilder);
        optionsBuilder
            .UseSqlServer("Server=localhost;Database=EfCore_TempKeySample_Sample;Integrated Security=true;TrustServerCertificate=True");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<Author>(b =>
        {
            b.HasOne(a => a.AuthorsClub)
                .WithMany()
                .HasForeignKey(a => a.AuthorsClubId);
        });
        modelBuilder.Entity<Book>(b =>
        {
            b.HasOne(book => book.Author)
                .WithMany()
                .HasForeignKey(book => book.AuthorId);
        });
    }

    public DbSet<Author> Authors { get; set; }
    public DbSet<Book> Books { get; set; }
}

sealed class AuthorsClub
{
    public int Id { get; set; }
    public string? Name { get; set; }
}
sealed class Author  
{
    public int Id { get; set; }
    public string? Name { get; set; }
    public int AuthorsClubId { get; set; }
    public AuthorsClub? AuthorsClub { get; set; }
}

sealed class Book
{
    public int Id { get; set; }
    public string? Title { get; set; }
    public int AuthorId { get; set; }
    public Author? Author { get; set; }
}

Setup

await using (MyDbContext dbContext = new())
{
    await dbContext.Database.EnsureDeletedAsync();
    await dbContext.Database.EnsureCreatedAsync();

    AuthorsClub authorsClubSouth = new()
    {
        Name = "AC South"
    };
    Author authorOfTheYear2022 = new()
    {
        Name = "Author of the year 2022",
        AuthorsClub = authorsClubSouth
    };
    Book book = new()
    {
        Title = "Fancy Book",
        Author = authorOfTheYear2022
    };
    dbContext.Add(authorsClubSouth);
    dbContext.Add(authorOfTheYear2022);
    dbContext.Add(book);
    await dbContext.SaveChangesAsync();
}

await using (MyDbContext dbContext = new())
{
    AuthorsClub authorsClubNorth = new()
    {
        Name = "AC North"
    };
    Author authorOfTheYear2023 = new()
    {
        Name = "Author of the year 2023",
        AuthorsClub = authorsClubNorth
    };
    dbContext.Add(authorsClubNorth);
    dbContext.Add(authorOfTheYear2023);

    Book book = await dbContext
        .Books
        .Include(b => b.Author)
        .SingleAsync();
    Author authorOfTheYear2022 = book.Author!;
    book.Author = authorOfTheYear2023;
    dbContext.Remove(authorOfTheYear2022);

    // The authorsClubSouth should not be touched.

    await dbContext.SaveChangesAsync(); // ❌
        // Microsoft.EntityFrameworkCore.DbUpdateException -> SqlException
        // The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Authors_AuthorsClub_AuthorsClubId".
        // The conflict occurred in database "EfCore_TempKeySample_Sample", table "dbo.AuthorsClub", column 'Id'.
}

Generated SQL

exec sp_executesql N'SET NOCOUNT ON;
INSERT INTO [AuthorsClub] ([Name])
OUTPUT INSERTED.[Id]
VALUES (@p0);
INSERT INTO [Authors] ([AuthorsClubId], [Name])
OUTPUT INSERTED.[Id]
VALUES (@p1, @p2);
',N'@p0 nvarchar(4000),@p1 int,@p2 nvarchar(4000)',@p0=N'AC North',@p1=-2147482646,@p2=N'Author of the year 2023'

Include provider and version information

EF Core version: 7.0 RC1
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 6.0 / .NET 7.0
Operating system: Windows 11
IDE: Visual Studio 2022 17.3.5

[ajcvickers]

Same root cause as #29318.

[JakobFerdinand]

@ajcvickers I´m not sure if that is correct.
at #29318 you wrote:

Note for triage: repros on latest daily; not a regression from 6.0 since in 6.0 the navigation property was not reset.

But if I change the EF version back to 6.0.10 for this example everything works.

[ajcvickers]

@roji @AndriySvyryd This looks like a regression in the update pipeline where a temporary FK value is being sent instead of getting the new generated value and using that. (Similar to #28654.)

Entities before saving are:

Author {Id: -2147482646} Added
  Id: -2147482646 PK Temporary
  AuthorsClubId: -2147482646 FK Temporary
  Name: 'Author of the year 2023'
  AuthorsClub: {Id: -2147482646}
Author {Id: 1} Unchanged
  Id: 1 PK
  AuthorsClubId: 1 FK
  Name: 'Alice'
  AuthorsClub: <null>
AuthorsClub {Id: -2147482646} Added
  Id: -2147482646 PK Temporary
  Name: 'AC North'
Book {Id: 1} Modified
  Id: 1 PK
  AuthorId: -2147482646 FK Modified Temporary Originally 1
  Title: <null>
  Author: {Id: -2147482646}

In EF7, we generate this:

info: 10/18/2022 10:17:53.393 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (2ms) [Parameters=[@p0='AC North' (Size = 4000), @p1='-2147482646', @p2='Author of the year 2023' (Size = 4000)], CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      INSERT INTO [AuthorsClub] ([Name])
      OUTPUT INSERTED.[Id]
      VALUES (@p0);
      INSERT INTO [Authors] ([AuthorsClubId], [Name])
      OUTPUT INSERTED.[Id]
      VALUES (@p1, @p2);

In EF Core 6.0, we generate this:

info: 10/18/2022 10:21:24.052 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (1ms) [Parameters=[@p0='AC North' (Size = 4000)], CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      INSERT INTO [AuthorsClub] ([Name])
      VALUES (@p0);
      SELECT [Id]
      FROM [AuthorsClub]
      WHERE @@ROWCOUNT = 1 AND [Id] = scope_identity();
info: 10/18/2022 10:21:24.058 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (0ms) [Parameters=[@p1='2', @p2='Author of the year 2023' (Size = 4000)], CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      INSERT INTO [Authors] ([AuthorsClubId], [Name])
      VALUES (@p1, @p2);
      SELECT [Id]
      FROM [Authors]
      WHERE @@ROWCOUNT = 1 AND [Id] = scope_identity();
info: 10/18/2022 10:21:24.062 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (1ms) [Parameters=[@p4='1', @p3='2'], CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      UPDATE [Books] SET [AuthorId] = @p3
      WHERE [Id] = @p4;
      SELECT @@ROWCOUNT;
info: 10/18/2022 10:21:24.065 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (1ms) [Parameters=[@p5='1'], CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      DELETE FROM [Authors]
      WHERE [Id] = @p5;
      SELECT @@ROWCOUNT;

Full code:

#nullable enable

using System;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

await using (MyDbContext dbContext = new())
{
    dbContext.Database.EnsureDeleted();
    dbContext.Database.EnsureCreated();

    dbContext.Add(
        new Book
        {
            Author = new Author()
            {
                Name = "Alice",
                AuthorsClub = new AuthorsClub()
                {
                    Name = "AC South"
                }
            }
        });

    dbContext.SaveChanges();
}

await using (MyDbContext dbContext = new())
{
    AuthorsClub authorsClubNorth = new()
    {
        Name = "AC North"
    };
    Author authorOfTheYear2023 = new()
    {
        Name = "Author of the year 2023",
        AuthorsClub = authorsClubNorth
    };
    dbContext.Add(authorsClubNorth);
    dbContext.Add(authorOfTheYear2023);

    Book book = await dbContext
        .Books
        .Include(b => b.Author)
        .SingleAsync();
    Author authorOfTheYear2022 = book.Author!;
    book.Author = authorOfTheYear2023;
    
    dbContext.ChangeTracker.DetectChanges();
    
    dbContext.Remove(authorOfTheYear2022);

    // The authorsClubSouth should not be touched.

    await dbContext.SaveChangesAsync(); // ❌
    // Microsoft.EntityFrameworkCore.DbUpdateException -> SqlException
    // The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Authors_AuthorsClub_AuthorsClubId".
    // The conflict occurred in database "EfCore_TempKeySample_Sample", table "dbo.AuthorsClub", column 'Id'.
}

sealed class MyDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        base.OnConfiguring(optionsBuilder);
        optionsBuilder
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging()
            .UseSqlServer(@"Data Source=(LocalDb)\MSSQLLocalDB;Database=AllTogetherNow");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<Author>(b =>
            {
                b.HasOne(a => a.AuthorsClub)
                    .WithMany()
                    .HasForeignKey(a => a.AuthorsClubId);
            });
        
        modelBuilder.Entity<Book>(b =>
            {
                b.HasOne(book => book.Author)
                    .WithMany()
                    .HasForeignKey(book => book.AuthorId);
            });
    }

    public DbSet<Author> Authors { get; set; }
    public DbSet<Book> Books { get; set; }
}

sealed class AuthorsClub
{
    public int Id { get; set; }
    public string? Name { get; set; }
}

sealed class Author  
{
    public int Id { get; set; }
    public string? Name { get; set; }
    public int AuthorsClubId { get; set; }
    public AuthorsClub? AuthorsClub { get; set; }
}

sealed class Book
{
    public int Id { get; set; }
    public string? Title { get; set; }
    public int AuthorId { get; set; }
    public Author? Author { get; set; }
}