8

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

Razvan Dumitru
  • 11,815
  • 5
  • 34
  • 54
Fabricio Rodriguez
  • 3,769
  • 11
  • 48
  • 101

1 Answers1

4

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.

Razvan Dumitru
  • 11,815
  • 5
  • 34
  • 54
  • 2
    Unfortunately that library *"at the moment supports ONLY MsSQL"*. – Ivan Stoev May 27 '19 at 15:27
  • The mechanism is the same for MySQL too. You'll end up with handling a raw SQL MERGE no matter what, but I mentioned that the example library works only under MsSQL. Thanks. – Razvan Dumitru May 27 '19 at 21:34
  • 1
    Thank you for your reply Razvan. I will investigate those libraries. The reason I want to drop actions which would result in adding a row with duplicate IDs is because this is for a chat application, and I am inserting message id's to a table to specify if a message was read, and by whom. Every time the client app requests new messages from the API (grabbed from the MySQL db), the API also INSERTS those message IDs to said table, indicating that the messages were read and by whom. If the client requests the same list of messages again, I don't want to re-insert these record id's into the table – Fabricio Rodriguez May 28 '19 at 13:09
  • 1
    This table is a simple table with three fields: messageId, userId and readDate. messageId and userId jointly form the primary key. If a user reads the same message twice, I do not want to INSERT two identical records into this table. The first time the user reads the message is all that I want to store. – Fabricio Rodriguez May 28 '19 at 13:11
  • Sounds like a good candidate for something that will get you to a raw sql merge where you'll do the following: WHEN MATCHED THEN UPDATE SET A.ID=A.ID or (in mysql you have) INSERT INTO t1 (MessageId,UserId,ReadDate) VALUES (1, 2, Date) ON DUPLICATE KEY UPDATE MessageId=MessageId; https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html – Razvan Dumitru May 28 '19 at 14:10
  • Either way, I don't think that you'll be able to find an extension for EFCore that will do this by default for you. So good luck! – Razvan Dumitru May 28 '19 at 14:12
  • Dropping new data is not that unusual in consideration of replaying events in context of time series, the unique constraint problem will come up a lot of the time. Data wont be different, its just that data may already exist. – Snazzie Sep 05 '22 at 16:26
  • It now supports SQLServer, MySQL, PostgreSQL and Sqlite – Magnetron Jan 25 '23 at 16:42