Skip duplicate records on bulk insert about dapper-plus

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