EntityFrameworkExtensions.BulkInsert: InsertIfNotExists is not working for ORACLE

[mentadev]

Description

The BulkInsert with the InsertIfNotExists is not working for ORACLE. The SQL Sentence generated only includes the INSERT without checking for existing records using the primary key (or the defined with the ColumnPrimaryKeyExpression option).

I'm attaching the SQL LOG for ORACLE and for PostgreSQL for the same c# code. As you notice for ORACLE it is not generating the "InsertIfNotExists" SQL Sentence therefore it always raise errors when inserting existing values.

Exception

The exception raised is the Primary key error that is raised by the Oracle engine: ORA-00001: restricción única (ETR.USER_REC_PK) violada.

Fiddle or Project (Optional)

Quite simple solution, the same you provide in the documentation https://dotnetfiddle.net/bNmy6f but execute it on ORA instead of SQL Server.

we tried with the two options, with the "ColumnPrimaryKeyExpression" and without the "ColumnPrimaryKeyExpression" definition but in ORA it always raise errors.

context.BulkInsert(customers, options => { options.InsertIfNotExists = true; options.ColumnPrimaryKeyExpression = c => c.Code; });

Further technical details

• EF version: EntityFramework Version="6.3.0" & Microsoft.EntityFrameworkCore Version="2.2.6
• EF Extensions version: Z.EntityFramework.Extensions.EFCore Version="2.7.9
• Database Provider: Oracle.EntityFrameworkCore Version="2.19.30
• Oracle: 18c XE

BulkInsert_ORACLE.txt BulkInsert_PostgreSQL.txt

[JonathanMagnan]

Hello @mentadev ,

Thank you for reporting,

I will assign this issue to my developer if the option is not implemented or cannot be supported in Oracle.

I should be able to provide an update tomorrow.

Best Regards,

Jon

[JonathanMagnan]

Hello @mentadev ,

My developer checked this issue. It looks that we don't support, however, he believes he has found a way to make it happens. He will continue his investigation today.

[mentadev]

Thanks Jonathan. We will buy the lincese anyway but we really would like to have this issue and the FormulaUpdate (https://github.com/zzzprojects/EntityFramework-Extensions/issues/298) working for ORACLE also. For us the "FormulaUpdate" issue is more critical than this "InsertIfNotExists" issue.

[JonathanMagnan]

Thank you @mentadev ,

We will highly focus this week on the FormulaUpdate as we have postponed this feature without any valid reason.

When I spoke to my developer this morning, we both agreed that we have everything ready to start to code the feature.

[JonathanMagnan]

Hello @mentadev ,

We tried to support during the last week the InsertIfNotExists options for Oracle.

Unfortunately, we didn't find any good way to make it happens.

We are currently limited by some syntax with Oracle and what version before 18 support and unfortunately, we didn't find any good way to support it yet.

At this moment, we choose to abandon it but we might eventually come back to try it again.

The only way left is making a fake BulkUpdate which will give us information about which row will exist. Something like that:

UPDATE TABLE
SET ColumnInt = ColumnInt
WHERE PK..
RETURNING...

So the column will be updated for the same value but that will give us the information needed to know if we should perform an insert or not from the RETURNING part. We don't really like this hack but if that's something you don't mind, we could support this syntax.