A failed bulk insert or update does not (always) tell which entities failed

[isak1080]

Description

During (some) failed updates, notably where the concurrency checks fail, the thrown exception has a list of entities that failed, making it very easy to retry the update with the failing entities removed.

For inserts (which by definition cannot have concurrency issues) or updates that fails due to a foreign key constraint violation for example, no such information is provided.

(Related issue: #284)

The reason I would like this is that I often do bulk updates and inserts and if they fail, I want to retry the operation with the subset of items that can be inserted/updated.

The workaround now is to either manually try to find the bad entities (very domain specific code) or retry the operation one by one, with possibly horrible performance.

Exception

The exception we get today is dependent on provider, but for me (using SQL Server in Azure) I get a SqlException with a "The MERGE statement conflicted with the FOREIGN KEY constraint..." error message. Nothing in the exception tells me which entity failed.

Fiddle or Project

This shows the problem: https://dotnetfiddle.net/DueeE4

Further technical details

• EF version: [EF Core v7.0.13]
• EF Extensions version: [EFE Core v7.100.0.0]
• Database Server version: [Azure SQL Server]

[JonathanMagnan]

Hello @isak1080 ,

Sorry for the very late answer.

This one is a very frequent request. Unfortunately, we are limited with the information SQL Server provides us, and at this moment, there is no way to have information about only which entities have failed. Only this general error is thrown by SQL Server.

I added in our backlog a method like ValidateEntities which will not save but return all entities in error due to a foreign key constraint or any other reason that we can validate on our side.

I cannot promise anything yet but that is surely something the next time we will choose some new options/methods to add that we will consider.

Feel free to ask for an update in 2024, it always help to make the feature happens ;)

Best Regards,

Jon