Problem Description

When inserting multiple related entities that utilize a join entity to define a many-many relationship at the same time with SaveChanges (regardless of whether batching is on) EF Core will generate a FK violation in the join table.

#Steps To Reproduce

Create a console application with the following properties:

<Project Sdk="Microsoft.NET.Sdk">

    <PropertyGroup>
        <OutputType>Exe</OutputType>
        <TargetFramework>net6.0</TargetFramework>
        <ImplicitUsings>enable</ImplicitUsings>
        <Nullable>enable</Nullable>
        <DockerDefaultTargetOS>Windows</DockerDefaultTargetOS>
    </PropertyGroup>

    <ItemGroup>
      <Content Include="..\.dockerignore">
        <Link>.dockerignore</Link>
      </Content>
    </ItemGroup>

    <ItemGroup>
      <PackageReference Include="Microsoft.EntityFrameworkCore" Version="7.0.2" />
      <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="7.0.2" />
    </ItemGroup>

</Project>

Run the sample code below (enhanced by @davidbaxterbrowne on my StackOverflow post), and the following exception will be thrown:

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.
 ---> Microsoft.Data.SqlClient.SqlException (0x80131904): The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Product_Rule_Product_ProductId". The conflict occurred in database "EFCoreScratchDatabase", table "dbo.Product", column 'ProductId'.
   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)
   at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.Consume(RelationalDataReader reader)
ClientConnectionId:375fb7a6-0af8-4d1e-a6aa-52296884204f
Error Number:547,State:0,Class:16
   --- End of inner exception stack trace ---
   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__107_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:\src\repos\EFCoreMultipleInsertWithJoinEntity\EFCoreMultipleInsertWithJoinEntity\EFCoreMultipleInsertWithJoinEntity\Program.cs:line 20

Sample Code

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

using var db = new Db();

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

var products = new List<Product>();
for (int i = 0; i < 2; i++)
{
    var p = new Product() {ProductDescription = $"Test{i}"};
    var r = new Rule() {RuleDescription = $"Test{i}"};

    p.Rules.Add(r);
    products.Add(p);
}

db.AddRange(products);
db.SaveChanges();

Console.WriteLine("Finished");


class Db : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(
                "Server=localhost;Database=EFCoreScratchDatabase;TrustServerCertificate=true;Integrated Security=true",
                o => { o.UseRelationalNulls().MaxBatchSize(1); })
            .LogTo(m => Console.WriteLine(m), LogLevel.Trace);
        optionsBuilder.EnableSensitiveDataLogging();
        base.OnConfiguring(optionsBuilder);
    }

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

        modelBuilder.Entity<Product>()
            .HasMany(product => product.Rules)
            .WithMany(rule => rule.Products)
            .UsingEntity<ProductRule>(
                join => join
                    .HasOne(productRule => productRule.Rule)
                    .WithMany()
                    .HasForeignKey(productRule => productRule.RuleId),
                join => join
                    .HasOne(productRule => productRule.Product)
                    .WithMany()
                    .HasForeignKey(productRule => productRule.ProductId),
                join =>
                {
                    join.ToTable("Product_Rule");
                    join.HasKey(e => e.ProductRuleId);
                });
    }
}

public class Rule
{
    public long RuleId { get; set; }

    public string RuleDescription { get; set; }

    public DateTime InsertedOn { get; set; }

    public ICollection<Product>? Products { get; set; } = new HashSet<Product>();
}

public class Product
{
    public long ProductId { get; set; }

    public string ProductDescription { get; set; }

    public DateTime InsertedOn { get; set; }

    public ICollection<Rule>? Rules { get; set; } = new HashSet<Rule>();
}

public class ProductRule
{
    public long ProductRuleId { get; set; }

    public long ProductId { get; set; }

    public long RuleId { get; set; }

    public Product Product { get; set; }

    public Rule Rule { get; set; }
}

Include provider and version information

EF Core version: 7.0.2 Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: .NET 6.0 Operating system: Windows 10 IDE: JetBrains Rider 2022.3.1

Jan 24 '23 16:01 tparikka

We've had multiple fixes in this area, can you please give the latest daily build a try and report how it goes? If that works, then the upcoming 7.0.3 should work as well.