search

mtanneryd / ef-bulk-operations

Bulk operations for Entity Framework 6
Apache License 2.0
80 stars 30 forks source link

BulkUpdateAll on view with instead of trigger, throws error #36

Open Arma-Branca opened 3 years ago

Arma-Branca commented 3 years ago

Hi, I'm currently using BulkUpdateAll to update view with an instead of trigger. I'm aware that this is somehow a strange usage but there's no easy workaround.

Problem

The BulkUpdateAll will try to do UPDATE t0 SET {properties} FROM to JOIN {temp table}, but due to SQLServer limitations this is not permitted for Views with instead of triggers, and throws the following error:

UPDATE is not allowed because the statement updates view "" which participates in a join and has an INSTEAD OF UPDATE trigger.

Solution

One way to solve this problem would be using a MERGE statement instead of the JOIN and maybe even enable the insertion of new elements in case of the flag InsertIfNew is enabled.

Is this something that could be done, or are there advantages in the usage of the UPDATE statement in terms of performance?

mtanneryd commented 3 years ago

Hi!

By pure coincidence I actually worked on another view-related issue with my bulk operations package for EF Core today. I'll go through your suggested changes as soon as I can.

Thanks!

/Måns

Måns Tånneryd Tånneryd IT AB Barrskogsvägen 19 186 53 Vallentuna +46-705140093 https://se.linkedin.com/in/manstanneryd

https://github.com/mtanneryd/ef6-bulk-operations https://github.com/mtanneryd/ef6-bulk-operations

Den tis 21 sep. 2021 kl 18:09 skrev Andre Almeida @.***

:

Hi, I'm currently using BulkUpdateAll to update view with an instead of trigger. I'm aware that this is somehow a strange usage but there's no easy work around. Problem

The BulkUpdateAll will try to do them UPDATE t0 SET {properties} FROM to JOIN {temp table}, but due to SQLServer limitations this is not permitted for Views with instead of triggers, and throws the following error:

UPDATE is not allowed because the statement updates view "" which participates in a join and has an INSTEAD OF UPDATE trigger.

Solution

One way to solve this problem would be using a MERGE statement instead of the JOIN and maybe even enable the insertion of new elements in case of the flag InsertIfNew is enabled.

Is this something that could be done, or are there advantages in the usage of the UPDATE statement in terms of performance?

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/mtanneryd/ef6-bulk-operations/issues/36, or unsubscribe https://github.com/notifications/unsubscribe-auth/AA2BSRP73PDXKRTTARMG35LUDCU4DANCNFSM5EO3RM3Q . Triage notifications on the go with GitHub Mobile for iOS https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Android https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.