OptimisticConcurrencyException - SQL 2008 R2 instead of inserting a trigger with Entity Framework

Question

Using the SQL 2008 R2 release database in November and the Azure.net 4.0 Beta 2 workspace role application, the work role collects data and inserts it into a single SQL table with a single identifier column. Since it is likely that several instances of this working role will be involved, I created an "Insert instead of a trigger" into the SQL table instead of the SQL query. The trigger executes the Upsert function using the SQL Merge function. Using T-SQL, I was able to verify the correct insertion instead of the trigger functions, new rows were inserted when updating existing rows. This is the code for my trigger:

Create Trigger [dbo].[trgInsteadOfInsert] on [dbo].[Cars] Instead of Insert as begin set nocount On merge into Cars as Target using inserted as Source on Target.id=Source.id AND target.Manufactureid=source.Manufactureid when matched then update set Target.Model=Source.Model, Target.NumDoors = Source.NumDoors, Target.Description = Source.Description, Target.LastUpdateTime = Source.LastUpdateTime, Target.EngineSize = Source.EngineSize when not matched then INSERT ([Manufactureid] ,[Model] ,[NumDoors] ,[Description] ,[ID] ,[LastUpdateTime] ,[EngineSize]) VALUES (Source.Manufactureid, Source.Model, Source.NumDoors, Source.Description, Source.ID, Source.LastUpdateTime, Source.EngineSize); End 

In the working role, I use the Entity Framework for the object model. When I call the SaveChanges method, I get the following exception:

 OptimisticConcurrencyException Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. Refresh ObjectStateManager entries. 

I understand that it looks like SQL is not reporting an IdentityScope return for each new inserted / updated row. Then EF considers that the rows were not inserted and the transaction was ultimately not completed.

What is the best way to handle this exception? Perhaps using OUTPUT from SQL merge function?

Thanks! -Paul

Answer 1

As you suspected, the problem is that each time you insert into a table with an Identity column, immediately select scope_identity () to populate the associated value in the Entity Framework. Instead of a trigger, this second step will be skipped, which will lead to an error with 0 lines.

I found the answer in https://stackoverflow.com/a/26617/... which suggested adding the following line at the end of your trigger (in the case when the element is not matched and the insert is done).

 select [Id] from [dbo].[TableXXX] where @@ROWCOUNT > 0 and [Id] = scope_identity() 

I tested this with Entity Framework 4.1 and it solved the problem for me. I completely copied all trigger creation for completeness. With this trigger restriction, I was able to add rows to the table by adding entities to the context and saving them using context.SaveChanges ().

 ALTER TRIGGER [dbo].[CalcGeoLoc] ON [dbo].[Address] INSTEAD OF INSERT AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT OFF; -- Insert statements for trigger here INSERT INTO Address (Street, Street2, City, StateProvince, PostalCode, Latitude, Longitude, GeoLoc, Name) SELECT Street, Street2, City, StateProvince, PostalCode, Latitude, Longitude, geography::Point(Latitude, Longitude, 4326), Name FROM Inserted; select AddressId from [dbo].Address where @@ROWCOUNT > 0 and AddressId = scope_identity(); END 

Answer 2

I had almost the same scenario: inserts with Entity Framework support in the view with the INSTEAD OF INSERT trigger on it led to the exception "... unexpected number of rows (0) ...". Thanks to Ryan Gross's answer, I fixed it by adding

 SELECT SCOPE_IDENTITY() AS CentrePersonID; 

at the end of my trigger, where CentrePersonID is the name of the key field of the base table that has the autocorrect ID. In this way, the EF can detect the identifier of the newly inserted record.