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 表上创建了一个 Insert 而不是触发器.触发器使用 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

在工作角色中，我使用实体框架作为对象模型.当我调用 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?

谢谢！-保罗

推荐答案

正如您所怀疑的，问题是任何插入到带有 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 对此进行了测试，它为我解决了这个问题.为了完整起见，我在这里复制了我的整个触发器创建.使用此触发器定义，我能够通过将地址实体添加到上下文并使用 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屋！