Ignore duplicate entries and commit successful ones on DbContext.SaveChanges() in EF Core

Question

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).

So for example:

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

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)

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.

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?

Thanks

Answer 1

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.

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.

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

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

• 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 

Which will do something like:

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) 

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