忽略重复的条目，并在EF Core中的DbContext.SaveChanges()上提交成功的条目 [英] Ignore duplicate entries and commit successful ones on DbContext.SaveChanges() in EF Core

问题描述

我有一个ASP .Net Core 2.2 Web API.在我的一个控制器操作中，我正在向MySQL数据库表添加一堆行(我正在使用Pomelo).

I have an ASP .Net Core 2.2 Web API. In one of my controller actions, I am adding a bunch of rows to a MySQL database table (I'm using Pomelo).

例如:

_dbContext.AddRange(entities);
_dbContext.SaveChanges();

我要添加的实体具有两个主键(复合主键)，并且当我将它们添加到DbContext时，这些键已经填充在实体集合中(即，我自己设置键-没有自动增量"或类似的在数据库生成密钥的地方进行的操作

The entities I'm adding have two primary keys (composite primary key) and the keys are already populated in the entities collection when I add them to DbContext (i.e. I am setting the keys myself - there is no "auto increment" or anything like that where the database generates the keys)

如果我要添加的任何实体已经存在于数据库中(就主键重复而言)，那么显然SaveChanges()会引发异常，并且整个事务都会回滚.

If any of the entities I'm adding already exist in the database, in terms of a duplicate primary key, then obviously SaveChanges() throws an exception, and the entire transaction rolls back.

是否有一种方法可以告诉EF Core忽略失败的实体?即忽略数据库中已经存在的实体，并提交成功的实体(即数据库中不存在的实体)?代替当前的行为是抛出异常并回滚整个事务?

Is there a way to tell EF Core to ignore the entities that failed? i.e. to ignore the entities that already existed in the database, and commit the entities that succeeded (i.e. that did not exist in the database)? Instead of the current behaviour which is to throw an exception and roll back the entire transaction?

谢谢

推荐答案

好像您遇到了业务问题.首先，您需要决定当您已经拥有一个具有相同ID的实体，而有人试图插入一个具有相同ID的新实体(新信息)时会发生什么.

Looks like you have a business problem in place. 1st you need to decide what will happen when you already have an entity with the same id in place and someone tries to insert a new one (new information) with the same id.

您似乎已经决定:要删除该动作.

It looks like you already decided: You want to drop the action.

这在某种程度上是不寻常的，因为如果您从该API的客户端接收到一些有关数据库中已经存在的实体的新数据->看起来更像是更新.

That's somehow unusual because if you receive some new data from clients of that API about an entity that already existed in your database -> that looks more like an Update.

存在一些可以执行类似操作的库: https://github.com/borisdj/EFCore .BulkExtensions (目前仅与MsSQL一起使用)

There exists some libraries that can do something similar: https://github.com/borisdj/EFCore.BulkExtensions (which is currently working only with MsSQL)

使用此库(这是一个已知的库，已被Microsoft称为EF核心工具:

Using this library (which is a known one and was already mentioned by Microsoft as being an EF Core Tool: https://docs.microsoft.com/en-us/ef/core/extensions/) you have the possibility to:

• 如果找到具有相同ID(Upsert)的实体，则插入或更新所有数据(所有列):

• Insert or Update all the data (all columns) if you find an entity with the same id (Upsert):

context.BulkInsertOrUpdateAsync(entitiesList);

将数据库中的实体与您从客户端收到的任何实体同步:

Synchronize entities from your database with whatever entities you receive from clients:

context.BulkInsertOrUpdateOrDeleteAsync(entitiesList);

大多数情况下，您可能找不到适合您的案例的东西，但是可以使用以下方法来调整该库:

Most probably you won't find something already implemented for your case but you can adjust this library with:

BulkInsertOrDropAsync 

将执行以下操作:

WHEN MATCHED THEN UPDATE SET A.ID=A.ID --The ID's are already the same so nothing will happen
WHEN NOT MATCHED THEN INSERT(A.ID,A.NAME,A.CODE,A.DESCRIPTION) 

这并不是真正的DROP，但是它将保留您的数据不变.

Which is not really a DROP, but it will leave your data intact.

这篇关于忽略重复的条目，并在EF Core中的DbContext.SaveChanges()上提交成功的条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！