Temporary Key is inserted as ForeignKey

[jluki]

I have a Book that has an n:m relationship with Author. The primary key of Book is a Guid and that of Author is int, which is additionally defined as a ColumnIdentity. There is an intermediate class BookAuthor with an int primary key Id, which is also defined as ColumnIdentity.

DemoDbContext looks like:

public class DemoDbContext : DbContext
{

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("Server=localhost;Database=DemoDb;Trusted_Connection=True;TrustServerCertificate=True;Integrated Security=true");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // handle keys
        modelBuilder.Entity<Author>().HasKey(a => a.Id);
        modelBuilder.Entity<Author>().Property(a => a.Id).UseIdentityColumn();

        modelBuilder.Entity<BookAuthor>().HasKey(ma => ma.Id);
        modelBuilder.Entity<BookAuthor>().Property(ma => ma.Id).UseIdentityColumn();

        modelBuilder.Entity<Book>().HasKey(b => b.Id);

        // handle table names
        modelBuilder.Entity<Author>().ToTable("Author");
        modelBuilder.Entity<Book>().ToTable("Book");
        modelBuilder.Entity<BookAuthor>().ToTable("BookAuthor");

        modelBuilder.Entity<Author>()
            .HasMany(author => author.Books)
            .WithMany(book => book.Authors)
            .UsingEntity<BookAuthor>();
    }

    public DbSet<Author> Authors { get; set; } = null!;

    public DbSet<Book> Books { get; set; } = null!;

    public DbSet<BookAuthor> BookAuthors { get; set; } = null!;
}

Now when I try to add some data with the following code, I get a conflict with a foreign key constraint.

using var db = new DemoDbContext();

Author marques = new Author { Name = "Gabriel García Márquez" };
Author goethe = new Author { Name = "Johann Wolfgang von Goethe" };

Book hundred = new Book { Title = "One Hundred Years of Solitude" };
Book faust = new Book { Title = "Faust" };

hundred.Authors.Add(marques);
faust.Authors.Add(goethe);

db.Books.Add(hundred);
db.Books.Add(faust);

db.SaveChanges();

results in

Microsoft.EntityFrameworkCore.DbUpdateException: 'An error occurred while saving the entity changes. See the inner exception for details.'

Inner Exception
SqlException: The MERGE statement conflicted with the FOREIGN KEY constraint "FK_BookAuthor_Author_AuthorId". The conflict occurred in database "DemoDb", table "dbo.Author", column 'Id'.

If I set a breakpoint on db.SaveChanges() and remove the foreign key constraint in the database, the SaveChanges() method succeeds. But in the BookAuthor table, the foreign key values for Authors contain the negative temporary keys and not the positive final keys.

I have attached a small solution that illustrates the problem: TempKeyBugExample.zip

The database tables look like:

Of course, you could add and save the authors beforehand. The following code works:

using var db = new DemoDbContext();

db.Database.EnsureDeleted();
db.Database.EnsureCreated();

Author marques = new Author { Name = "Gabriel García Márquez" };
Author goethe = new Author { Name = "Johann Wolfgang von Goethe" };

Book hundred = new Book { Title = "One Hundred Years of Solitude" };
Book faust = new Book { Title = "Faust" };

db.Authors.Add(marques);
db.Authors.Add(goethe);
db.SaveChanges();

hundred.Authors.Add(marques);
faust.Authors.Add(goethe);

db.Books.Add(hundred);
db.Books.Add(faust);

db.SaveChanges();

But shouldn't the first code mentioned work from scratch? Am I missing something?

provider and version information

EF Core version: 7.0.10
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 7.0
Operating system: Windows 11 Pro
IDE: Visual Studio 2022 17.7.2

[ajcvickers]

@roji @AndriySvyryd Looks like a bug in the update pipeline when inserting into a join table with an Identity column.

Repro:

using (var context = new SomeDbContext())
{
    await context.Database.EnsureDeletedAsync();
    await context.Database.EnsureCreatedAsync();

    var marques = new Author { Name = "Gabriel García Márquez" };
    var goethe = new Author { Name = "Johann Wolfgang von Goethe" };
    var hundred = new Book { Title = "One Hundred Years of Solitude" };
    var faust = new Book { Title = "Faust" };

    hundred.Authors.Add(marques);
    faust.Authors.Add(goethe);

    context.Books.Add(hundred);
    context.Books.Add(faust);

    context.SaveChanges();
}

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

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Author>()
            .HasMany(author => author.Books)
            .WithMany(book => book.Authors)
            .UsingEntity<BookAuthor>();
    }

    public DbSet<Author> Authors { get; set; } = null!;
    public DbSet<Book> Books { get; set; } = null!;
    public DbSet<BookAuthor> BookAuthors { get; set; } = null!;
}

public class Book
{
    public Guid Id { get; set; }
    public string? Title { get; set; }
    public ICollection<Author> Authors { get; set; } = new List<Author>();
}

public class BookAuthor
{
    public int Id { get; set; }
    public Guid BookId { get; set; }
    public int AuthorId { get; set; }
}

public class Author
{
    public int Id { get; set; }
    public string? Name { get; set; }
    public ICollection<Book> Books { get; set; } = new List<Book>();
}

Stack trace:

Unhandled exception. Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.
 ---> Microsoft.Data.SqlClient.SqlException (0x80131904): The MERGE statement conflicted with the FOREIGN KEY constraint "FK_BookAuthors_Authors_AuthorId". The conflict occurred in database "AllTogetherNow", table "dbo.Authors", column 'Id'.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
   at Microsoft.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)
   at Microsoft.Data.SqlClient.SqlDataReader.Read()
   at Microsoft.EntityFrameworkCore.Storage.RelationalDataReader.Read()
   at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.ConsumeResultSet(Int32 startCommandIndex, RelationalDataReader reader)
ClientConnectionId:b812e0dd-d3fd-40f5-a1be-512d985e7647
Error Number:547,State:0,Class:16
   --- End of inner exception stack trace ---
   at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.ConsumeResultSet(Int32 startCommandIndex, RelationalDataReader reader)
   at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.Consume(RelationalDataReader reader)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.SqlServer.Update.Internal.SqlServerModificationCommandBatch.Execute(IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(IEnumerable`1 commandBatches, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Storage.RelationalDatabase.SaveChanges(IList`1 entries)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IList`1 entriesToSave)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(StateManager stateManager, Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.<>c.<SaveChanges>b__112_0(DbContext _, ValueTuple`2 t)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChanges()
   at Program.<Main>$(String[] args) in C:\local\code\AllTogetherNow\Daily\Daily.cs:line 33
   at Program.<Main>(String[] args)