Skip duplicate records on bulk insert

[daniel-parra-issatec]

Hello. I wrote a function that receives a list of objects and bulk inserts them in a MySql database table.

However, when there is a duplicate record on the list or it already exists on the database, the bulk insert fails because of a unique constraint on the table.

This is part of the code on how I'm doing the bulk insert: List patients <- received by a function, validated at the front end so it won't be null or empty.

DapperPlusManager.Entity("Patient_KeepIdentity").Table("Patients").Identity(x => x.patientId); db.BulkInsert("Patient_KeepIdentity", patients);

I've been looking for some bulk option like "ignore duplicate" but there is nothing on the documentation or the options displayed on visual studio.

I also tried a bulk merge but it didn't work and IgnoreOnInsertExpression skips inserting on specific columns which is not wham I'm looking for.

Exception

Exception message: MySqlConnector.MySqlException (0x80004005): Duplicate entry
Stack trace:
MySqlConnector.MySqlException (0x80004005): Duplicate entry '10001112233-1' for key 'pacientes.pacientes_unique_index'
   at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in /_/src/MySqlConnector/Core/ResultSet.cs:line 43
   at MySqlConnector.MySqlDataReader.ActivateResultSet(CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 130
   at MySqlConnector.MySqlDataReader.NextResultAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 90
   at MySqlConnector.MySqlDataReader.NextResult() in /_/src/MySqlConnector/MySqlDataReader.cs:line 23
   at Z.BulkOperations.MySqlDataAdapter.Fill(DbDataReader reader, DataSet ds)
   at .(DbCommand , BulkOperation )
   at .( , DbCommand )
   at .Execute(List`1 actions)
   at Z.BulkOperations.BulkOperation.Execute()
   at Z.Dapper.Plus.DapperPlusAction.Execute()
   at Z.Dapper.Plus.DapperPlusAction..ctor(BaseDapperPlusActionSet action, String key, DapperPlusActionKind kind, Object dataSource)
   at Z.Dapper.Plus.DapperPlusActionSet`1.AddAction(String mapperKey, DapperPlusActionKind actionKind, TEntity item)
   at Z.Dapper.Plus.DapperPlusActionSet`1.DapperPlusActionSetBuilder(DapperPlusContext context, IDbConnection connection, IDbTransaction transaction, String mapperKey, DapperPlusActionKind actionKind, TEntity item, Func`2[] selectors)
   at Z.Dapper.Plus.DapperPlusExtensions.BulkMerge[T](IDbConnection connection, String mapperKey, T item, Func`2[] selectors)
   at MyHome.DAL.Repositories.CargaRepository.CargarPacientes(List`1 pacientes) in C:\Users\dparra.ISSATEC.000\Documents\Oficina\Repos\MyHome-V2\server\MyHome.DAL\Repositories\CargaRepository.cs:line 50

I'm not sure if I missed some configuration regarding this.

Further technical details

• Dapper version: [Dapper v2.0.143]
• Dapper Plus version: [Dapper Plus v6.0.1]
• Database Server version: [MySql 8.0]
• Database Provider version (NuGet): [MySqlconnector v2.2.6]

Thanks in advance for your help.

[JonathanMagnan]

Hello @daniel-parra-issatec ,

To handle this scenario correctly, you need 2 options:

• AllowDuplicateKeys = true: Will only insert the last row that contains the same key (within the same batch)
• InsertIfNotExists = true: Will only insert if the data doesn't already exist in the database

Here is 2 examples:

// mapping options on the entity
DapperPlusManager.Entity("Patient_KeepIdentity")
    .Table("Patients")
    .Identity(x => x.patientId)
    .UseBulkOptions(x =>
    {
        x.AllowDuplicateKeys = true;
        x.InsertIfNotExists = true;
    });
db.BulkInsert("Patient_KeepIdentity", patients);

// mapping option on the connection
connection.UseBulkOptions(x =>
{
    x.AllowDuplicateKeys = true;
    x.InsertIfNotExists = true;
}).BulkInsert("Patient_KeepIdentity", patients);

Let me know if that solves your issue correctly.

Best Regards,

Jon

[JonathanMagnan]

Hello @daniel-parra-issatec,

Since our last conversation, we haven't heard from you.

Were you able to solve your issue?

Let me know if you need more information.

Best regards,

Jon

[daniel-parra-issatec]

Hi,

Sorry for the late reply. I tried what you suggested on the connection but I keep getting the exception error. Maybe it's a database thing because there's already a record there with the same combination of identification number and document type (that's the unique index) and that's why it won't let me insert anything. It looks like the InsertIfNotExists option is having a conflict with the table's unique index.

Best regards,

Daniel.

[JonathanMagnan]

Hello @daniel-parra-issatec ,

So if I understand correctly, your key is currently the patientId.

So, our library currently checks correctly if there is an existing row with the current parentId or not. However, something a row with a parentId that doesn't exist tries to be inserted in the database, and this insertion fails due to a unique index constraint on 2 other columns:

• IdentificationNumber
• DocumentType

If that's the case, is it for this particular BulkInsert the parentId the right key to use? If both your properties (IdentificationNumber and DocumentType) are always populated, would it be better to use this one as the key? Since this is a unique index, it also makes every row unique in the database.

If you still have the problem or that's not the right solution, do you think you could create a runnable project with the issue? It doesn’t need to be your project, just a new solution with the minimum code to reproduce the issue. It will allow us to better understand your scenario and provide the right solution.

Best Regards,

Jon

[daniel-parra-issatec]

Hi Jon,

Looks like I was finally able to solve it. You were right: the patientId shouldn't be the table identity for bulk insert and instead I used id number and document type by mapping all the table fields, then using .Key(x => new { x.id_numbre,x.documentType}).InsertIfNotExists(); I still left the AllowDuplicateKeys option on the connection. So it seems to be working now.

Thank you so much for your help.

Best regards,

Daniel.