SaveChanges creates an empty Merge query statement, transactions are probably not garanteed, consequential damage: Microsoft.Data.SqlClient.SqlException (0x80131904): The MERGE statement conflicted with the FOREIGN KEY constraint

[HenkKin]

We have a table in Sql Server with 77 columns. When we save a lot of entities in this table with Entity Framework 7 then EF7 is generating multiple batched Merge sql-statements.

When we add a new column, number 78, it is generating multiple batches Merge sql-statements but one of them is an empty statement with a lot of sql parameters, but without query body. De query runs fine, so our assumption is that the transactions will succeed, but the entities are not stored in the database.
In our case there are dependent entities (Foreign Key relationships), the next query is complaining

Consequential damage

Exception throw by next query with FK relationship:

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_DependentEntity_PrincipalEntity_PrincipalId". The conflict occurred in database "[DatabaseName]", table "[TableName]", column 'Id'.

This FK constraint is complaining because in the previous query the Principal entity is not stored in the database and the primary key is not returned to the entity, so FK is pointing to a non existing id.

Assumptions:

• empty statement is running, maybe transactions are not guaranteed and will succeed without inserting records.
• The number of batches will be generated based on sql-parameter count (max 2100 for Sql Server provider?)
• Setting the MaxBatchSize on DbContext will work in some test cases, but not all. Default is 1000. Setting explicit to 1000 will work for some which are failing with default MaxBatchSize (not setting explicit).

Workaround:

• Setting the MaxBatchSize on DbContext to 1 will force EF7 to generate single insert statements in stead of merge statements

Provider and version information

EF Core version: 7.0.0, 7.0.1
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 7.0
Operating system: Windows
IDE: Visual Studio 2022 17.1

[roji]

@HenkKin can you please provide a minimal, runnable code sample that shows this happening (preferably as a simplified console program)? Did you also encounter this issue on EF Core 6.0, or is it new in EF Core 7.0?

[HenkKin]

@HenkKin can you please provide a minimal, runnable code sample that shows this happening (preferably as a simplified console program)? Did you also encounter this issue on EF Core 6.0, or is it new in EF Core 7.0?

In .net 7 it works until adding the number 78 column. But I think is was working in EF Core 6.0.

The change in my opinion is: https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-7.0/whatsnew#faster-savechanges

I will have a look for a reproducible situation.

[roji]

One more thing before spending too much work on a repro: can you please try with the latest 8.0 daily build? We've already fixed several bugs in the new SaveChanges implementation, so it's worth checking if the bug is already gone.

[HenkKin]

Today we tested with EF Core 8.0.0-* daily build packages and it works! The issue is in EF Core 7.0.0 and EF Core 7.0.1 releases. When can we expect a new version with this solution?

[roji]

If this is #29502, then 7.0.2 contains the fix (to be released mid-January). Otherwise, the remaining fixes will be released with 7.0.3 in mid-February.

You should be OK with using the 8.0.0 daily build for now - these are very close to the 7.0 builds and are quite stable.

Am going to close this as a dup, but feel free to continue posting here if you have more questions/comments.

[roji]

Duplicate of #29502