OptimisticConcurrencyException -- SQL 2008 R2 Instead of Insert Trigger with Entity Framework

Question

Using a SQL 2008 R2 November release database and a .net 4.0 Beta 2 Azure worker role application. The worker role collects data and inserts it into a single SQL table with one identity column. Because there will likely be multiple instances of this worker role running, I created an Insert Instead Of trigger on the SQL table. The trigger performs Upsert functionality using the SQL Merge function. Using T-SQL I was able to verify the insert instead of trigger functions correctly, new rows were inserted while existing rows were updated. 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

Within the worker role I am using Entity Framework for an object model. When I call the SaveChanges method I receieve 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 this is likly due to SQL not reporting back an IdentityScope for each new inserted/updated row. Then EF thinks the rows were not inserted and the transaction is not ultimately not committed.

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

Thanks! -Paul

Answer 1

As you suspected, the problem is that any insertions into a table with an Identity column are immediately followed by a select of the scope_identity() to populate the associated value in the Entity Framework. The instead of trigger causes this second step to be missed, which leads to the 0 rows inserted error.

I found an answer in this StackOverflow thread that suggested adding the following line at the end of your trigger (in the case where the item is not matched and the Insert is performed).

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 have copied my entire trigger creation here for completeness. With this trigger defenition I was able to add rows to the table by adding Address 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