title: Why does EF Core inserts before deleting instead of deleting and then inserting when updating a child list of a model?
body: <p>I'm Entity Framework Core 3 and I'm using the Pomelo MySQL provider.
  I'm trying to update a child property with:</p>
  
  <pre class="lang-cs prettyprint-override"><code>model.Items = collection;
  </code></pre>
  
  <p>where <code>model</code> is an entity that exists in the database and <code>collection</code> is a <code>ICollection&lt;Item&gt;</code> of new entities to be set for that list.</p>
  
  <p>When I then run:</p>
  
  <pre class="lang-cs prettyprint-override"><code>dbContext.Models.Update(model);
  await dbContext.SaveChangesAsync(ct);
  </code></pre>
  
  <p>I notice a strange behavior (by looking at the EF Core query logs): previously existing items associated with the model are not removed immediately but only after the new items in <code>collection</code> are inserted. </p>
  
  <p>This causes an integrity constraint error on the database because for a brief moment (between the insertion and later deletion) there are duplicate values for certain unique columns.</p>
  
  <p>I'm wondering if this behavior is configurable somehow and why are related entities removed only after insertion when viceversa seems safer. </p>
  
  <hr>
  
  <h3>How to reproduce</h3>
  
  <p>In order to reproduce the issue you can use this <code>docker-compose.yml</code> file to setup a MySQL 8 database:</p>
  
  <pre><code>version: '3.4'
  
  services:
  
    database:
      image: mysql:8.0
      environment:
        MYSQL_USER: So61383388
        MYSQL_PASSWORD: root
        MYSQL_ROOT_PASSWORD: root
        MYSQL_DATABASE: So61383388
      ports:
        - "3306:3306"
        - "33060:33060"
  </code></pre>
  
  <p>and then run this program:</p>
  
  <pre class="lang-cs prettyprint-override"><code>using System.Collections.Generic;
  using System.Diagnostics;
  using System.Linq;
  using Microsoft.EntityFrameworkCore;
  using Microsoft.Extensions.Logging;
  
  namespace IssueConsoleTemplate
  {
      public class IceCream
      {
          public int IceCreamId { get; set; }
          public string Name { get; set; }
  
          public ICollection&lt;IceCreamVariation&gt; Variations { get; set; } = new HashSet&lt;IceCreamVariation&gt;();
      }
  
      public class IceCreamVariation
      {
          public int IceCreamVariationId { get; set; }
          public string Name { get; set; }
          public int IceCreamId { get; set; }
  
          public IceCream IceCream { get; set; }
  
          public ICollection&lt;IceCreamVariationQuality&gt; Qualities { get; set; } = new HashSet&lt;IceCreamVariationQuality&gt;();
      }
  
      public class IceCreamVariationQuality
      {
          public int IceCreamVariationQualityId { get; set; }
          public string Name { get; set; }
          public int IceCreamVariationId { get; set; }
  
          public IceCreamVariation IceCreamVariation { get; set; }
      }
  
      public class Context : DbContext
      {
          public DbSet&lt;IceCream&gt; IceCreams { get; set; }
          public DbSet&lt;IceCreamVariation&gt; IceCreamVariations { get; set; }
  
          protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
          {
              optionsBuilder
                  .UseMySql(
                      "server=127.0.0.1;port=3306;user=root;password=root;database=So61383388",
                      b =&gt; b.ServerVersion("8.0"))
                  .UseLoggerFactory(
                      LoggerFactory.Create(
                          b =&gt; b
                              .AddConsole()
                              .AddFilter(level =&gt; level &gt;= LogLevel.Information)))
                  .EnableSensitiveDataLogging()
                  .EnableDetailedErrors();
          }
  
          protected override void OnModelCreating(ModelBuilder modelBuilder)
          {
              modelBuilder.Entity&lt;IceCream&gt;()
                  .HasData(
                      new IceCream {IceCreamId = 1, Name = "Vanilla"}
                  );
  
              modelBuilder.Entity&lt;IceCreamVariation&gt;()
                  .HasData(
                      new IceCreamVariation {IceCreamVariationId = 1, Name = "Double Vanilla Bourbon", IceCreamId = 1},
                      new IceCreamVariation {IceCreamVariationId = 2, Name = "Vanilla Caramel", IceCreamId = 1}
                  );
  
              modelBuilder.Entity&lt;IceCreamVariationQuality&gt;()
                  .HasData(
                      new IceCreamVariationQuality {IceCreamVariationQualityId = 1, Name = "Fresh", IceCreamVariationId = 1},
                      new IceCreamVariationQuality {IceCreamVariationQualityId = 2, Name = "Yummy", IceCreamVariationId = 1},
                      new IceCreamVariationQuality {IceCreamVariationQualityId = 3, Name = "Woops", IceCreamVariationId = 2}
                  );
          }
      }
  
      internal class Program
      {
          private static void Main()
          {
              using (var context = new Context())
              {
                  context.Database.EnsureDeleted();
                  context.Database.EnsureCreated();
  
                  var iceCreamWithOldVariations = context.IceCreams
                      .Include(i =&gt; i.Variations)
                          .ThenInclude(i =&gt; i.Qualities)
                      .OrderBy(i =&gt; i.IceCreamId)
                      .FirstOrDefault();
  
                  Debug.Assert(iceCreamWithOldVariations.Variations.Count == 2);
  
                  var vanillaIceCream = iceCreamWithOldVariations;
                  vanillaIceCream.Variations.Clear();
                  vanillaIceCream.Variations.Add(
                      new IceCreamVariation 
                      {
                          Name = "Vanilla Cheesecake", 
                          Qualities = new IceCreamVariationQuality[]
                          {
                              new IceCreamVariationQuality { Name = "Healthy" },
                          },
                      });
                  vanillaIceCream.Variations.Add(
                      new IceCreamVariation 
                      {
                          Name = "Vanilla Cheesecake", 
                          Qualities = new IceCreamVariationQuality[]
                          {
                              new IceCreamVariationQuality { Name = "Fresh" },
                              new IceCreamVariationQuality { Name = "Cool" },
                          },
                      });
  
                  context.SaveChanges();
  
                  var iceCreamWithNewVariations = context.IceCreams
                      .Include(i =&gt; i.Variations)
                          .ThenInclude(i =&gt; i.Qualities)
                      .OrderBy(i =&gt; i.IceCreamId)
                      .FirstOrDefault();
  
                  Debug.Assert(iceCreamWithNewVariations.Variations.Count == 2);
              }
          }
      }
  }
  </code></pre>
  
  <p>with this console .csproj:</p>
  
  <pre><code>&lt;Project Sdk="Microsoft.NET.Sdk"&gt;
  
    &lt;PropertyGroup&gt;
      &lt;OutputType&gt;Exe&lt;/OutputType&gt;
      &lt;TargetFramework&gt;netcoreapp3.1&lt;/TargetFramework&gt;
    &lt;/PropertyGroup&gt;
  
    &lt;ItemGroup&gt;
      &lt;PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="3.1.1" /&gt;
      &lt;PackageReference Include="Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore" Version="3.1.0" /&gt;
      &lt;PackageReference Include="Microsoft.Extensions.Logging.Console" Version="3.1.1" /&gt;
    &lt;/ItemGroup&gt;
  
  &lt;/Project&gt;
  </code></pre>
  
  <p>using:</p>
  
  <pre><code>docker-compose down
  docker-compose up -d
  dotnet run
  </code></pre>
  
  <p>If you see the logs:</p>
  
  <ul>
  <li>the qualities are removed first (which is ok)</li>
  <li>then new variations are added (which is incorrect)</li>
  <li>the old variations are removed (which should happen before the new variations are added)</li>
  </ul>
  
  <p>This behavior doesn't seem to happen if you don't have the nested qualities for each variation.</p>