Trigger on table causes OptimisticConcurrencyException

[RCozens]

Hi,

I'm getting the below error on an insert.
It only happens when my trigger is enabled (which instead inserts the data in to an inherited table.).
The trigger follows the Postgresql example at the bottom of 5.9.2 on the following page
http://www.postgresql.org/docs/9.3/stat ... oning.html
I understand that it is being caused because when using such a trigger the insert returns null (instead of new) but i cannot find a satisfactory resolution when using Postgresql.
Do you know how i can fix?
I would be happy to disable EF6 ConcurrencyCheck but I don't see how.

Thanks
Russell

Code: Select all

System.Data.Entity.Core.OptimisticConcurrencyException was unhandled
  HResult=-2146233087
  Message=Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=472540 for information on understanding and handling optimistic concurrency exceptions.
  Source=EntityFramework
  StackTrace:
       at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.ValidateRowsAffected(Int64 rowsAffected, UpdateCommand source)
       at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update()
       at System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.<Update>b__2(UpdateTranslator ut)
       at System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.Update[T](T noChangesResult, Func`2 updateFunction)
       at System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.Update()
       at System.Data.Entity.Core.Objects.ObjectContext.<SaveChangesToStore>b__35()
       at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
       at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesToStore(SaveOptions options, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction)
       at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass2a.<SaveChangesInternal>b__27()
       at System.Data.Entity.Infrastructure.DefaultExecutionStrategy.Execute[TResult](Func`1 operation)
       at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesInternal(SaveOptions options, Boolean executeInExistingTransaction)
       at System.Data.Entity.Core.Objects.ObjectContext.SaveChanges(SaveOptions options)
       at System.Data.Entity.Core.Objects.ObjectContext.SaveChanges()
       at WindowsFormsApplication4.Form1.button1_Click(Object sender, EventArgs e)

[Shalex]

We recommend you to map CUD operations of an entity to functions in PostgreSQL.

Implement concurrency control basing on https://msdn.microsoft.com/en-us/data/gg699321.aspx.

Entity Developer documentation: http://www.devart.com/entitydeveloper/e ... eloper.chm > ORM Support > Entity Framework > Concepts > Stored Procedure Mapping.

Be aware that you should upgrade to the newest (7.4.592) build of dotConnect for PostgreSQL which includes an important fix ("The bug with executing stored procedure with output parameter is fixed").

[RCozens]

Thank you for your response.
If I understand you correctly I need to use CUD operations but I do not have any as this is a trigger function rather than a stored procedure.
I cannot create any stored procedures either as I cannot modify database design.

Are there any solutions that don't involve altering database design?

[Shalex]

Consider the following alternative approaches:

1. Usage of fake PKs. Set StoreGeneratedPattern of your PK column in SSDL to None and assing pseudo values for PK in your code (they will be ignored when inserting records in the database). After SaveChanges(), you should dispose current context (not to use entities in cache with fake IDs), create new DbContext/ObjectContext and retrieve records from database with actual IDs.

2. Put the body of a function in the SSDL part of your model (instead of creating function itself in database) for implementing CUD operations: http://www.devart.com/entitydeveloper/e ... eloper.chm > ORM Support > Entity Framework > Concepts > Working with Methods> Command Text > Command Text Without Result.

[RCozens]

The first option would work for me but I have had trouble implementing it.
To test I am inserting one row,
I've set StoreGeneratedPattern to None. (Looking in the SQL log I can see that the INSERT no longer has a RETURNING clause)
I've set a value in code for my PK column
Now, SaveChanges() includes my PK column and value and raises the same OPTIMISTICCONCURRENCYEXCEPTION

[Shalex]

Please localize the issue and send us a small test project with the corresponding DDL/DML script for reproducing.