Support for Incremental Updates in BulkInsertOrUpdateAsync

[davidbezdek96]

Hi,

I've encountered a limitation when working with BulkInsertOrUpdateAsync in EF Core BulkExtensions and would like to ask how to handle this scenario or suggest a potential feature enhancement.

Scenario

I need to perform incremental updates on numeric columns during a bulk operation. Specifically, I want to add a delta value from the inserted data to the existing value in the database for each conflicting row.

Here’s an example of what I’m trying to achieve in SQL:

INSERT INTO "GenericTable" ("EntityId", "NumericField1", "NumericField2", "NumericField3", "LastModified") 
VALUES (?, ?, ?, ?, ?) 
ON CONFLICT ("EntityId") DO UPDATE 
SET 
    "NumericField1" = "GenericTable"."NumericField1" + EXCLUDED."NumericField1",
    "NumericField2" = "GenericTable"."NumericField2" + EXCLUDED."NumericField2",
    "NumericField3" = "GenericTable"."NumericField3" + EXCLUDED."NumericField3",
    "LastModified" = EXCLUDED."LastModified";

When using BulkInsertOrUpdateAsync, I cannot find a way to override the default SET clause logic for the ON CONFLICT part of the query to implement the incremental behavior shown above.

Current Implementation

I tried using PropertiesToIncludeOnUpdate to specify the columns for updates and assumed I could customize the SQL for the SET clause using OnConflictUpdateWhereSql. However, it seems OnConflictUpdateWhereSql is intended only for conditions and cannot modify the SET behavior.

Example code:

await context.BulkInsertOrUpdateAsync(entities.ToList(), c =>
{
    c.UpdateByProperties = new List<string> { nameof(GenericEntity.EntityId) };

    c.PropertiesToIncludeOnUpdate = new List<string>
    {
        nameof(GenericEntity.NumericField1),
        nameof(GenericEntity.NumericField2),
        nameof(GenericEntity.NumericField3),
        nameof(GenericEntity.LastModified)
    };

    c.OnConflictUpdateWhereSql = (existing, inserted) => $@"
        {existing}.NumericField1 = {existing}.NumericField1 + {inserted}.NumericField1,
        {existing}.NumericField2 = {existing}.NumericField2 + {inserted}.NumericField2,
        {existing}.NumericField3 = {existing}.NumericField3 + {inserted}.NumericField3,
        {existing}.LastModified = {inserted}.LastModified";
});

This throws a syntax error because the incremental logic should be part of the SET clause, not WHERE. After reviewing the documentation and source code, I couldn’t find a way to achieve this.

Workaround

As a workaround, I’m currently implementing the logic in my application:

Load the existing records into memory using a bulk load.
Increment the values in the application.
Perform a bulk update with the modified records.
While this works, it introduces additional roundtrips to the database and is less efficient than handling the logic directly in SQL.

Question

Is there a way to override or customize the SET clause for ON CONFLICT in BulkInsertOrUpdateAsync? If not, would you consider adding support for such functionality?

This feature would be beneficial for scenarios like mine, where operations require incremental updates instead of direct overwrites.

Environment
EF Core BulkExtensions: 8.1.2
Database: PostgreSQL
EF Core: 8.0.11

Thank you for your time and consideration. If there’s a recommended way to handle this scenario that I might have missed, I’d greatly appreciate any guidance.

Best regards,
David