I'm Entity Framework Core 3 and I'm using the Pomelo MySQL provider. I'm trying to update a child property with:
model.Items = collection;
where model is an entity that exists in the database and collection is a ICollection<Item> of new entities to be set for that list.
When I then run:
dbContext.Models.Update(model);
await dbContext.SaveChangesAsync(ct);
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 collection are inserted.
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.
I'm wondering if this behavior is configurable somehow and why are related entities removed only after insertion when viceversa seems safer.
In order to reproduce the issue you can use this docker-compose.yml file to setup a MySQL 8 database:
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"
and then run this program:
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<IceCreamVariation> Variations { get; set; } = new HashSet<IceCreamVariation>();
}
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<IceCreamVariationQuality> Qualities { get; set; } = new HashSet<IceCreamVariationQuality>();
}
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<IceCream> IceCreams { get; set; }
public DbSet<IceCreamVariation> 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 => b.ServerVersion("8.0"))
.UseLoggerFactory(
LoggerFactory.Create(
b => b
.AddConsole()
.AddFilter(level => level >= LogLevel.Information)))
.EnableSensitiveDataLogging()
.EnableDetailedErrors();
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<IceCream>()
.HasData(
new IceCream {IceCreamId = 1, Name = "Vanilla"}
);
modelBuilder.Entity<IceCreamVariation>()
.HasData(
new IceCreamVariation {IceCreamVariationId = 1, Name = "Double Vanilla Bourbon", IceCreamId = 1},
new IceCreamVariation {IceCreamVariationId = 2, Name = "Vanilla Caramel", IceCreamId = 1}
);
modelBuilder.Entity<IceCreamVariationQuality>()
.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 => i.Variations)
.ThenInclude(i => i.Qualities)
.OrderBy(i => 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 => i.Variations)
.ThenInclude(i => i.Qualities)
.OrderBy(i => i.IceCreamId)
.FirstOrDefault();
Debug.Assert(iceCreamWithNewVariations.Variations.Count == 2);
}
}
}
}
with this console .csproj:
<Project Sdk="Microsoft.NET.Sdk">
<PropertyGroup>
<OutputType>Exe</OutputType>
<TargetFramework>netcoreapp3.1</TargetFramework>
</PropertyGroup>
<ItemGroup>
<PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="3.1.1" />
<PackageReference Include="Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore" Version="3.1.0" />
<PackageReference Include="Microsoft.Extensions.Logging.Console" Version="3.1.1" />
</ItemGroup>
</Project>
using:
docker-compose down
docker-compose up -d
dotnet run
If you see the logs:
This behavior doesn't seem to happen if you don't have the nested qualities for each variation.
For a workaround, see "Workaround" below.
What is your specific reason for using DbSet.Update? It has as very special purpose that has to do with tracking.
Instead, it should be enough to remove the old items and adding the new:
model.Items.Remove(someOldItem); // or use other `Remove` methods
model.Items.AddRange(newItems);
context.SaveChanges();
Here is a complete example how it works:
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<IceCreamVariation> Variations { get; set; } = new HashSet<IceCreamVariation>();
}
public class IceCreamVariation
{
public int IceCreamVariationId { get; set; }
public string Name { get; set; }
public int IceCreamId { get; set; }
public IceCream IceCream { get; set; }
}
public class Context : DbContext
{
public DbSet<IceCream> IceCreams { get; set; }
public DbSet<IceCreamVariation> IceCreamVariations { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder
.UseMySql(
"server=127.0.0.1;port=3306;user=root;password=;database=So61383388",
b => b.ServerVersion("8.0.20-mysql"))
.UseLoggerFactory(
LoggerFactory.Create(
b => b
.AddConsole()
.AddFilter(level => level >= LogLevel.Information)))
.EnableSensitiveDataLogging()
.EnableDetailedErrors();
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<IceCream>()
.HasData(
new IceCream {IceCreamId = 1, Name = "Vanilla"},
new IceCream {IceCreamId = 2, Name = "Chocolate"}
);
modelBuilder.Entity<IceCreamVariation>()
.HasData(
new IceCreamVariation {IceCreamVariationId = 1, Name = "Double Vanilla Bourbon", IceCreamId = 1},
new IceCreamVariation {IceCreamVariationId = 2, Name = "Vanilla Caramel", IceCreamId = 1},
new IceCreamVariation {IceCreamVariationId = 3, Name = "Chocolate Hazelnut", IceCreamId = 2}
);
}
}
internal class Program
{
private static void Main()
{
using (var context = new Context())
{
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
var iceCreamsWithOldVariations = context.IceCreams
.Include(i => i.Variations)
.OrderBy(i => i.IceCreamId)
.ToList();
Debug.Assert(iceCreamsWithOldVariations.Count == 2);
Debug.Assert(iceCreamsWithOldVariations[0].Variations.Count == 2);
Debug.Assert(iceCreamsWithOldVariations[1].Variations.Count == 1);
var vanillaIceCream = iceCreamsWithOldVariations[0];
var vanillaCaramelVariation = iceCreamsWithOldVariations[0].Variations.First();
vanillaIceCream.Variations.Remove(vanillaCaramelVariation);
vanillaIceCream.Variations.Add(new IceCreamVariation {Name = "Vanilla Cheesecake"});
vanillaIceCream.Variations.Add(new IceCreamVariation {Name = "Vanilla-Lemon"});
var cholocateIceCream = iceCreamsWithOldVariations[1];
cholocateIceCream.Variations.Clear();
cholocateIceCream.Variations.Add(new IceCreamVariation {Name = "Chocolate Fudge Brownie"});
cholocateIceCream.Variations.Add(new IceCreamVariation {Name = "Chocolate-Peanut Butter"});
context.SaveChanges();
var iceCreamsWithNewVariations = context.IceCreams
.Include(i => i.Variations)
.OrderBy(i => i.IceCreamId)
.ToList();
Debug.Assert(iceCreamsWithNewVariations.Count == 2);
Debug.Assert(iceCreamsWithNewVariations[0].Variations.Count == 3);
Debug.Assert(iceCreamsWithNewVariations[1].Variations.Count == 2);
}
}
}
}
The following SQL statements are being generated, which work as expected:
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (6ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE DATABASE `So61383388`;
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (38ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE `IceCreams` (
`IceCreamId` int NOT NULL AUTO_INCREMENT,
`Name` longtext CHARACTER SET utf8mb4 NULL,
CONSTRAINT `PK_IceCreams` PRIMARY KEY (`IceCreamId`)
);
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (35ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE `IceCreamVariations` (
`IceCreamVariationId` int NOT NULL AUTO_INCREMENT,
`Name` longtext CHARACTER SET utf8mb4 NULL,
`IceCreamId` int NOT NULL,
CONSTRAINT `PK_IceCreamVariations` PRIMARY KEY (`IceCreamVariationId`),
CONSTRAINT `FK_IceCreamVariations_IceCreams_IceCreamId` FOREIGN KEY (`IceCreamId`) REFERENCES `IceCreams` (`IceCreamId`) ON DELETE CASCADE
);
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (6ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
INSERT INTO `IceCreams` (`IceCreamId`, `Name`)
VALUES (1, 'Vanilla');
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
INSERT INTO `IceCreams` (`IceCreamId`, `Name`)
VALUES (2, 'Chocolate');
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (6ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
INSERT INTO `IceCreamVariations` (`IceCreamVariationId`, `IceCreamId`, `Name`)
VALUES (1, 1, 'Double Vanilla Bourbon');
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
INSERT INTO `IceCreamVariations` (`IceCreamVariationId`, `IceCreamId`, `Name`)
VALUES (2, 1, 'Vanilla Caramel');
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
INSERT INTO `IceCreamVariations` (`IceCreamVariationId`, `IceCreamId`, `Name`)
VALUES (3, 2, 'Chocolate Hazelnut');
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (30ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE INDEX `IX_IceCreamVariations_IceCreamId` ON `IceCreamVariations` (`IceCreamId`);
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT `i`.`IceCreamId`, `i`.`Name`, `i0`.`IceCreamVariationId`, `i0`.`IceCreamId`, `i0`.`Name`
FROM `IceCreams` AS `i`
LEFT JOIN `IceCreamVariations` AS `i0` ON `i`.`IceCreamId` = `i0`.`IceCreamId`
ORDER BY `i`.`IceCreamId`, `i0`.`IceCreamVariationId`
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (7ms) [Parameters=[@p0='1', @p1='3', @p2='1', @p3='Vanilla Cheesecake' (Size = 4000), @p4='1', @p5='Vanilla-Lemon' (Size = 4000), @p6='2', @p7='Chocolate Fudge Brownie' (Size = 4000), @p8='2', @p9='Chocolate-Peanut Butter' (Size = 4000)], CommandType='Text', CommandTimeout='30']
DELETE FROM `IceCreamVariations`
WHERE `IceCreamVariationId` = @p0;
SELECT ROW_COUNT();
DELETE FROM `IceCreamVariations`
WHERE `IceCreamVariationId` = @p1;
SELECT ROW_COUNT();
INSERT INTO `IceCreamVariations` (`IceCreamId`, `Name`)
VALUES (@p2, @p3);
SELECT `IceCreamVariationId`
FROM `IceCreamVariations`
WHERE ROW_COUNT() = 1 AND `IceCreamVariationId` = LAST_INSERT_ID();
INSERT INTO `IceCreamVariations` (`IceCreamId`, `Name`)
VALUES (@p4, @p5);
SELECT `IceCreamVariationId`
FROM `IceCreamVariations`
WHERE ROW_COUNT() = 1 AND `IceCreamVariationId` = LAST_INSERT_ID();
INSERT INTO `IceCreamVariations` (`IceCreamId`, `Name`)
VALUES (@p6, @p7);
SELECT `IceCreamVariationId`
FROM `IceCreamVariations`
WHERE ROW_COUNT() = 1 AND `IceCreamVariationId` = LAST_INSERT_ID();
INSERT INTO `IceCreamVariations` (`IceCreamId`, `Name`)
VALUES (@p8, @p9);
SELECT `IceCreamVariationId`
FROM `IceCreamVariations`
WHERE ROW_COUNT() = 1 AND `IceCreamVariationId` = LAST_INSERT_ID();
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT `i`.`IceCreamId`, `i`.`Name`, `i0`.`IceCreamVariationId`, `i0`.`IceCreamId`, `i0`.`Name`
FROM `IceCreams` AS `i`
LEFT JOIN `IceCreamVariations` AS `i0` ON `i`.`IceCreamId` = `i0`.`IceCreamId`
ORDER BY `i`.`IceCreamId`, `i0`.`IceCreamVariationId`
I'm wondering if this behavior is configurable somehow and why are related entities removed only after insertion when viceversa seems safer.
You can see, that the DELETE statements are being executed before the INSERT statements.
Update
With the new sample code you provided, I am able to reproduce the issue. This seems to be a bug in EF Core (not Pomelo), because the same behavior can be reproduced with SQL Server:
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<IceCreamVariation> Variations { get; set; } = new HashSet<IceCreamVariation>();
}
public class IceCreamVariation
{
public int IceCreamVariationId { get; set; }
public string Name { get; set; }
public int UniqueId { get; set; }
public int IceCreamId { get; set; }
public IceCream IceCream { get; set; }
public ICollection<IceCreamVariationQuality> Qualities { get; set; } = new HashSet<IceCreamVariationQuality>();
}
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<IceCream> IceCreams { get; set; }
public DbSet<IceCreamVariation> IceCreamVariations { get; set; }
public DbSet<IceCreamVariationQuality> IceCreamVariationQualities { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder
.UseSqlServer(@"Data Source=.\MSSQL14;Integrated Security=true;Initial Catalog=So61383388_01")
//.UseMySql(
// "server=127.0.0.1;port=3308;user=root;password=;database=So61383388_01",
// b => b.ServerVersion("8.0.20-mysql"))
.UseLoggerFactory(
LoggerFactory.Create(
b => b
.AddConsole()
.AddFilter(level => level >= LogLevel.Information)))
.EnableSensitiveDataLogging()
.EnableDetailedErrors();
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<IceCream>()
.HasData(
new IceCream {IceCreamId = 1, Name = "Vanilla"}
);
modelBuilder.Entity<IceCreamVariation>(
entity =>
{
entity.HasAlternateKey(e => e.UniqueId);
entity.HasData(
new IceCreamVariation
{
IceCreamVariationId = 1,
Name = "Double Vanilla Bourbon",
UniqueId = 42, // this value is part of a unique index
IceCreamId = 1
}
);
});
modelBuilder.Entity<IceCreamVariationQuality>()
.HasData(
new IceCreamVariationQuality
{
IceCreamVariationQualityId = 1,
Name = "Yummy",
IceCreamVariationId = 1
}
);
}
}
internal class Program
{
private static void Main()
{
using (var context = new Context())
{
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
var iceCreamWithOldVariations = context.IceCreams
.Include(i => i.Variations)
.ThenInclude(i => i.Qualities)
.OrderBy(i => i.IceCreamId)
.First();
Debug.Assert(iceCreamWithOldVariations.Variations.Count == 1);
Debug.Assert(iceCreamWithOldVariations.Variations.Single().UniqueId == 42);
Debug.Assert(iceCreamWithOldVariations.Variations.Single().Qualities.First().Name == "Yummy");
iceCreamWithOldVariations.Variations.Clear();
iceCreamWithOldVariations.Variations.Add(
new IceCreamVariation
{
Name = "Vanilla Cheesecake",
UniqueId = 42, // use same value again; should work because previous entity was removed
Qualities = new[]
{
new IceCreamVariationQuality { Name = "Healthy" },
},
});
context.SaveChanges();
var iceCreamWithNewVariations = context.IceCreams
.Include(i => i.Variations)
.ThenInclude(i => i.Qualities)
.OrderBy(i => i.IceCreamId)
.First();
Debug.Assert(iceCreamWithNewVariations.Variations.Count == 1);
Debug.Assert(iceCreamWithNewVariations.Variations.Single().UniqueId == 42);
Debug.Assert(iceCreamWithNewVariations.Variations.Single().Qualities.First().Name == "Healthy");
}
}
}
}
If executed when the UniqueId property has been declared, an exception is being thrown and the generated SQL is even more unexpected:
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (15ms) [Parameters=[@p0='1'], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
DELETE FROM [IceCreamVariationQualities]
WHERE [IceCreamVariationQualityId] = @p0;
SELECT @@ROWCOUNT;
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (3ms) [Parameters=[@p1='1', @p0='Vanilla Cheesecake' (Size = 4000)], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
UPDATE [IceCreamVariations] SET [Name] = @p0
WHERE [IceCreamVariationId] = @p1;
SELECT [IceCreamVariationId]
FROM [IceCreamVariations]
WHERE @@ROWCOUNT = 1 AND [IceCreamVariationId] = scope_identity();
Here, an UPDATE statement has been generated to change the name of a IceCreamVariations entity (which is wrong), that assumes the entity has just been inserted (which is not the case), because it uses scope_identity().
Workaround:
The discussion continued on GitHub.
There, @smitpatel suggested to try entity.HasIndex(e => e.UniqueId).IsUnique() instead of entity.HasAlternateKey(e => e.UniqueId), which works as expected.
So defining a unique index instead of an alternate key in the model definition is a valid workaround for this issue:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
// ...
modelBuilder.Entity<IceCreamVariation>(
entity =>
{
// This does not work:
// entity.HasAlternateKey(e => e.UniqueId);
// This *does* work:
entity.HasIndex(e => e.UniqueId)
.IsUnique();
entity.HasData(
new IceCreamVariation
{
IceCreamVariationId = 1,
Name = "Double Vanilla Bourbon",
UniqueId = 42, // this value is part of a unique index
IceCreamId = 1
}
);
});
// ...
}