EF Core 6 Insert using the Add method failing when table is linked with a trigger

[srivathsanvlb]

I have a postgresql table to which I have linked a before insert trigger and trigger function. In the trigger function based on the insert values, I create child tables using inheritance and insert the value to the child table. I return NULL value in the function so that the insert doesn't happen to the parent table.

When I perform insert using Insert query in pgadmin, it is working fine ie child table is created dynamically based on inheritance and value gets entered to the child table. When queried the parent using select, it scans parent and child and merges the records to give the output.

The insert works fine (as explained above) when I use execute in .NET using ADO.NET using the Npgsql library and execute the above insert query. I executed the same insert query using EF core 6 (Dbfirst approach, optionsBuilder,UseNpgsql is used in the context) using context.Database.ExecuteSqlRaw(sqlQuery) statement and it works fine too.

But when I try to use EF core 6 again and use the Add statement on the scaffolded database model of the parent table it fails with the error

Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException: The database operation was expected to affect 1 row(s), but actually affected 0 row(s); data may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.
         at Npgsql.EntityFrameworkCore.PostgreSQL.Update.Internal.NpgsqlModificationCommandBatch.Consume(RelationalDataReader reader)
         at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
         at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(IEnumerable`1 commandBatches, IRelationalConnection connection)
         at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IList`1 entriesToSave)
         at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(StateManager stateManager, Boolean acceptAllChangesOnSuccess)
         at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.<>c__DisplayClass31_0`2.<Execute>b__0(DbContext context, TState state)
         at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementation[TState,TResult](Func`3 operation, Func`3 verifySucceeded, TState state)
         at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
         at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
         at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)

Let me know if you would like to see the scripts, I will send them privately.

[roji]

In general, when you do SaveChanges, EF checks how many rows were affected, and if it's not the expected number, raises a DbUpdateConcurrencyException; this is a concurrency mechanism to detect if some e.g. deleted a row just before you are able to update it.

I'm not very familiar with the inner details of triggers, but if your trigger causes the INSERT to say that "0 rows were inserted", that would cause this. There may be a way to make the INSERT still return "1 row was inserted", even if that row isn't to the original table but to a different table (via the trigger).

Alternatively, dotnet/efcore#10443 tracks allowing you to disable the concurrency checks on the EF side; but this isn't implemented at this point.

/cc @ajcvickers

[srivathsanvlb]

Please find attached the required details. Please let me know if you need any additional information. S. Srivathsan.

…

On Tue, Oct 11, 2022 at 8:39 PM Shay Rojansky ***@***.***> wrote: In general, when you do SaveChanges, EF checks how many rows were affected, and if it's not the expected number, raises a DbUpdateConcurrencyException; this is a concurrency mechanism to detect if some e.g. deleted a row just before you are able to update it. I'm not very familiar with the inner details of triggers, but if your trigger causes the INSERT to say that "0 rows were inserted", that would cause this. There may be a way to make the INSERT still return "1 row was inserted", even if that row isn't to the original table but to a different table (via the trigger). Alternatively, dotnet/efcore#10443 <dotnet/efcore#10443> tracks allowing you to disable the concurrency checks on the EF side; but this isn't implemented at this point. /cc @ajcvickers <https://github.com/ajcvickers> Yeah, please submit a minimal database schema and a minimal EF code sample (ideally a runnable console program) which shows the exception happening. — Reply to this email directly, view it on GitHub <#2521 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AIPD7O4IEFPQTYBVEECA44TWCV7LNANCNFSM6AAAAAARCJQUQ4> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[roji]

@srivathsanvlb I'm not seeing any attached required details.

[srivathsanvlb]

I have sent it as a word document attachment via email so that my scripts need not be exposed to the public. Doesn't it work that way? Let me know the right way to do it. Update : sent them to the email ID roji@roji.org. Hope it helps. S. Srivathsan.

…

On Wed, Oct 12, 2022 at 11:54 AM Shay Rojansky ***@***.***> wrote: @srivathsanvlb <https://github.com/srivathsanvlb> I'm not seeing any attached required details. — Reply to this email directly, view it on GitHub <#2521 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AIPD7O3RS5XBHDLUVL6UI43WCZKSTANCNFSM6AAAAAARCJQUQ4> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[srivathsanvlb]

@roji - Hope you were able to get the scripts via email. Kindly let me know if I have to share by any other means.

[roji]

@srivathsanvlb apologies, I'm going over old issues and noticed this; I'm not sure whether I actually received the script or not.

If this issue is still relevant, can you please resend?

[srivathsanvlb]

I am resending the scripts to this github thread via email and also to your email ***@***.*** Please check and let me know if you haven't received it. FYI, we moved to hypertables since the dynamic inheritance approach didn't provide the performance we required. But I would like to get an update on this issue.

…

On Thu, Mar 9, 2023 at 4:55 PM Shay Rojansky ***@***.***> wrote: @srivathsanvlb <https://github.com/srivathsanvlb> apologies, I'm going over old issues and noticed this; I'm not sure whether I actually received the script or not. If this issue is still relevant, can you please resend? — Reply to this email directly, view it on GitHub <#2521 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AIPD7OZVCSVUQLCPKUGW753W3G43ZANCNFSM6AAAAAARCJQUQ4> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[roji]

@srivathsanvlb I received your message, and it seems like what I wrote in this above comment is more or less what's happening. EF checks how many rows PG reports to have been inserted, verifying that it's 1 rows and not zero. In addition, in your case it reads back the database-generated value for the key - this is why you can see a RETURNING clause appended at the end of the INSERT statement.

The trigger is interfering with this mechanism, causing nothing to get returned, as if the insert didn't actually insert anything, and that's why EF is throwing. There may be a way to tweak your trigger configuration so that it still returns the expected details, but I don't know offhand how one would do that. Otherwise, dotnet/efcore#10443 tracks allowing you to disable this check entirely.

Note that EF Core 7.0 allows mapping inserts (and updates/deletes) to stored procedures. That would allow you to call a PG procedure directly for inserting, rather than using a trigger which replaces the inserting. If you're interested, that would probably be a way to do this.

I'm going to go ahead and close this issue as a duplicate of dotnet/efcore#10443, but please feel free to post back and ask any further questions you have.

[roji]

Duplicate of dotnet/efcore#10443