OptimisticConcurrencyException - SQL 2008 R2而不是使用实体框架插入触发器 [英] OptimisticConcurrencyException -- SQL 2008 R2 Instead of Insert Trigger with Entity Framework

问题描述

使用SQL 2008 R2 11月版本数据库和.net 4.0 Beta 2 Azure工作人员角色应用程序。工作者角色收集数据并将其插入到具有一个标识列的单个SQL表中。因为这个工作角色可能会有多个实例运行，所以我在SQL表上创建了一个插入而不是触发器。触发器使用SQL Merge功能执行Upsert功能。使用T-SQL我能够正确地验证插入而不是触发函数，而在更新现有行时插入新行。
这是我的触发器的代码：

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

在工作者角色中，我正在使用Entity Framework作为对象模型。当我调用SaveChanges方法时，我收到以下异常：

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.

我明白这是因为SQL不会为每个新插入/更新的行报告一个IdentityScope。那么EF认为这些行没有插入，并且事务最终不会被提交。

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.

处理此异常的最佳方法是什么？可能使用SQL合并功能中的OUTPUT？

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

谢谢！
-Paul

Thanks! -Paul

推荐答案

如您所怀疑的那样，问题是任何插入到具有Identity列的表格中都会立即然后选择一个scope_identity（）来填充实体框架中的关联值。而不是触发器会导致错过第二个步骤，这会导致0行插入错误。

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.

我在这个StackOverflow线程，建议添加在触发结束的下一行（如果项目不匹配，插入执行）。

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() 

我用Entity Framework 4.1测试了这个，它解决了我的问题我已经将我的整个触发器创建在这里复制完整。通过这个触发器defenition，我可以通过向上下文添加地址实体来添加行，并使用context.SaveChanges（）。

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

这篇关于OptimisticConcurrencyException - SQL 2008 R2而不是使用实体框架插入触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！