Devart Forums

Posted: **Thu 11 Feb 2016 18:22**

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)

Posted: **Fri 12 Feb 2016 16:50**

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").

Posted: **Tue 16 Feb 2016 15:13**

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?

Posted: **Wed 17 Feb 2016 08:49**

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.

Posted: **Fri 19 Feb 2016 13:57**

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

Posted: **Mon 22 Feb 2016 11:25**

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

Devart Forums

Trigger on table causes OptimisticConcurrencyException

Trigger on table causes OptimisticConcurrencyException

Re: Trigger on table causes OptimisticConcurrencyException

Re: Trigger on table causes OptimisticConcurrencyException

Re: Trigger on table causes OptimisticConcurrencyException

Re: Trigger on table causes OptimisticConcurrencyException

Re: Trigger on table causes OptimisticConcurrencyException