Throw better exception for a unique index cycle during update #15319
Description
Describe what is not working as expected.
I have a pretty simple model. ( I have also attached a solution with db project and a basic console app to to reproduce the error). When the program runs, it will clean the tables, populate some data, swap the values and then try to save. You'll just need to set the connection string to a test database on line 19 of MyDBContext.cs
Tables are as follows:
- Users
- Location
- UserLocation (Many to Many) between 1 & 2.
In addition I have a unique index on UserLocation that has a filter. When I try to update (basically swap the values for two rows), I am getting a unique index violation.
When I profile the sql, EF Core does not issue the update commands in the right order.
The actual production bug we have currently is a little more complex than this, but I have basically distilled it down to this.
If you are seeing an exception, include the full exceptions details (message and stack trace).
Exception message:
SqlException: Cannot insert duplicate key row in object 'dbo.UserLocation' with unique index 'UIX_UserLocation_Priority'. The duplicate key value is (1, 1).
The statement has been terminated.
Stack trace:
| Name | Value | Type
-- | -- | -- | --
| Source | "Core .Net SqlClient Data Provider" | string
| Name | Value | Type
-- | -- | -- | --
| StackTrace | " at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)\r\n at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)\r\n at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)\r\n at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()\r\n at System.Data.SqlClient.SqlDataReader.get_MetaData()\r\n at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)\r\n at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)\r\n at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)\r\n at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)\r\n at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)\r\n at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)" | string
Steps to reproduce
Include a complete code listing (or project/solution) that we can run to reproduce the issue.
Partial code listings, or multiple fragments of code, will slow down our response or cause us to push the issue back to you to provide code to reproduce the issue.
Console.WriteLine("Hello World!");Further technical details
EF Core version: (found in project.csproj or packages.config)
CoreUpdateWrongOrderIssue.zip
CoreUpdateWrongOrderIssue.zip
Database Provider: (e.g. Microsoft.EntityFrameworkCore.SqlServer)
Operating system:
IDE: (e.g. Visual Studio 2017 15.4)
Activity