Support bulk insert on tables with triggers

[kragoth]

Is your feature request related to a problem? Please describe.

BulkInsert does not support inserting into tables that have triggers.
The sql generated for bulk insert is basically

MERGE [schema].[table] AS t USING [schema].[#tmp_tbl_name] AS s 
ON 1=2 WHEN NOT MATCHED THEN INSERT ([col1],[col2]) 
OUTPUT $Action,[s].[_be_xx_id],[inserted].[Id],[inserted].[RowVer],[inserted].[TemporalEndTime],[inserted].[TemporalStartTime] ;

This results in the error: The target table 't' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.

Describe the solution you'd like
Given the following discussions in EF around the topic: dotnet/efcore#27372
and on stackoverflow: https://stackoverflow.com/questions/13198476/cannot-use-update-with-output-clause-when-a-trigger-is-on-the-table

It seems that the only solution would be to generate an alternative insert statement if the table has a trigger.

Describe alternatives you've considered
I have used ZEntityFramework.Plus to do bulk inserts successfully. So I assume that they are not using the merge statement. But, it was a while ago and so I'm not 100% sure it was on a table with a trigger. I would need to go test it out.

[NorthernLight1]

kragoth,

The merge statement is used internally to support many of the options for the bulk operations.

I did some testing and discovered that if I remove the output from the merge statement, it would fix the issue you are having with the trigger. If you do use the framework this way, any database generated fields in the EF entities you are saving, will not be populated again after saving.

Proposed Fix: If BulkOptions.AutoMapOutput is set to false, then the OUTPUT portion of the merge statement will no longer be generated.

[kragoth]

@NorthernLight1 yeah if the output is not mandatory then yeah, that would be a great fix. What's the effect of removing the output?

[NorthernLight1]

Kragoth,

The output is used to set any database generated fields, like an Id or CreatedDate fields, but it does result in a small performance overhead.

This fix is present in the latest version (v8.0.0.6)

[kragoth]

@NorthernLight1 legend! We'll give it a go. :)

[kragoth]

@NorthernLight1 unfortunately that doesn't work. It seems to be because of this line of code

                var columnsToOutput = GetMergeOutputColumns(autoGeneratedColumns, delete);

This always adds Action and Id to the output columns so an output statement is always added to the sql command.

[NorthernLight1]

kragoth

I will release another version soon and patch it.

[NorthernLight1]

kragoth,

Please try again with 8.0.0.7. The issue should be resolved.

[kragoth]

@NorthernLight1 this is working for us now. Thank you!