DbUpdateException should not leave the context in a bad state

[yonidagan]

EF is wrongly adding (persisting) an entity when a duplicate key is found.

Consider the following Entity, when Name is set as Alternate Key:

  public class MyEntity
    {
        [Key]
        public int Id { get; set; }
        
        // name is AlternateKey, set by modelBuilder
        public string Name { get; set; }
        public string SomeValue { get; set; }
    }

Then consider the following scenario:

1. Add Entity1 with name "Foo".
2. SaveChanges -> Db is updated correctly and table now contains Entity1
3. Add Entity2 with name "Foo" -> exception is thrown (duplicate key) (see exception below)
4. Delete Entity1.
5. 5. Save Changes -> DB is updated so that Entity1 is deleted but Entity2 is saved!
      One would not expect Entity1 to be saved after an error occured when adding it to the Dbset.
      Yet, it looks like it is left in DbSet.Local and, with an Added State and is Added to the Db once Entity1 is removed.

Exception on step 3 is:

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.
---> Microsoft.Data.SqlClient.SqlException (0x80131904): Violation of UNIQUE KEY constraint 'AK_MyEntities_Name'. Cannot insert duplicate key in object 'dbo.MyEntities'. The duplicate key value is (Foo).
The statement has been terminated.

The following code simulates that process:

try
{
    MyDbContext db = new MyDbContext();

    MyEntity e1 = new MyEntity() { Name = "Foo", SomeValue = "Value1" };
    db.MyEntities.Add(e1);
    db.SaveChanges();

    MyEntity e2 = new MyEntity() { Name = "Foo", SomeValue = "Value2" };
    try
      {
        db.MyEntities.Add(e2);
      }
      catch (Exception ex)
      // Exception is thrown here
      {
        Console.WriteLine(ex);
       }
  db.MyEntities.Remove(e1);
  db.SaveChanges();
  // e1 is deleted but e2 is persisted in the Db!
}
catch (Exception ex)
{
  Console.WriteLine(ex);
}

It seems the DbSet.Local is wrongly updated with the entity even though an exception was thrown during the call to .Add
A full console app sample project to reproduce this issue is attached.

---

EF Core version: 6.0.8
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 6.0
EFDuplicateKey.zip

[ajcvickers]

Note for triage: The context should be considered in an invalid state after an InvalidOperationException has been thrown--dotnet/EntityFramework.Docs#3401 tracks documenting this better. We should discuss if this also applies to DbUpdateException. (Note that it does not apply to DbUpdateConcurrencyException.)

[ajcvickers]

Note from triage: DbUpdateException should not leave the context in a bad state.