小贝子编程

为什么 EF Core 在更新模型的子列表时先插入删除，然后再插入?



我是Entity Framework Core 3，我正在使用Pomelo MySQL提供程序。 我正在尝试使用以下方法更新子属性：

model.Items = collection;

其中model是数据库中存在的实体，collection是为该列表设置的新实体的ICollection<Item>。

当我然后运行时：

dbContext.Models.Update(model);
await dbContext.SaveChangesAsync(ct);

我注意到一种奇怪的行为(通过查看 EF Core 查询日志)：以前与模型关联的现有项目不会立即删除，而只会在插入collection中的新项目后删除。

这会导致数据库出现完整性约束错误，因为在短暂的时刻(在插入和稍后删除之间)，某些唯一列存在重复值。

我想知道这种行为是否以某种方式可配置，为什么只有在 viceversa 看起来更安全时插入后才删除相关实体。

如何复制

为了重现该问题，您可以使用此docker-compose.yml文件来设置MySQL 8数据库：

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"

然后运行此程序：

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);
}
}
}
}

使用此控制台 .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>

用：

docker-compose down
docker-compose up -d
dotnet run

如果您看到日志：

  • 首先去除品质(没关系)
  • 然后添加新的变体(这是不正确的)
  • 删除旧变体(这应该在添加新变体之前发生)

如果您没有每个变体的嵌套品质，则此行为似乎不会发生。

有关解决方法，请参阅下面的"解决方法"。

您使用 DbSet.Update 的具体原因是什么？它具有与跟踪有关的非常特殊的目的。

相反，删除旧项目并添加新项目就足够了：

model.Items.Remove(someOldItem); // or use other `Remove` methods
model.Items.AddRange(newItems);
context.SaveChanges();

下面是它的工作原理的完整示例：

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);
}
}
}
}

正在生成以下 SQL 语句，这些语句按预期工作：

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`

我想知道这种行为是否以某种方式可配置，为什么只有在 viceversa 看起来更安全时插入后才删除相关实体。

您可以看到，DELETE语句是在INSERT语句之前执行的。

更新

使用您提供的新示例代码，我能够重现该问题。这似乎是EF Core(不是Pomelo)中的一个错误，因为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");
}
}
}
}

如果在声明UniqueId属性时执行，则会引发异常，生成的 SQL 更加出乎意料：

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();

在这里，生成了一个UPDATE语句来更改IceCreamVariations实体的名称(这是错误的)，该语句假定该实体刚刚入(事实并非如此)，因为它使用scope_identity().

解决方法：

讨论在GitHub上继续进行。

在那里，@smitpatel建议尝试entity.HasIndex(e => e.UniqueId).IsUnique()而不是entity.HasAlternateKey(e => e.UniqueId)，它按预期工作。

因此，在模型定义中定义唯一索引而不是备用键是解决此问题的有效解决方法：

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
}
);
});
// ...
}

相关内容

最新更新


热门标签：

javascript python java c# php android html jquery c++ css ios sql mysql arrays asp.net json python-3.x ruby-on-rails .net sql-server django objective-c excel regex ruby linux ajax iphone xml vba spring asp.net-mvc database wordpress string postgresql wpf windows xcode bash git oracle list vb.net multithreading eclipse algorithm macos powershell visual-studio image forms numpy scala function api selenium