在UPDATE上使用Entity Framework的OptimisticConcurrencyException会影响0行 [英] OptimisticConcurrencyException with Entity Framework on an UPDATE, affects 0 rows

查看：184
本文介绍了在UPDATE上使用Entity Framework的OptimisticConcurrencyException会影响0行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一个数据结构：



SecurityPolicy 1< --- * SecurityPolicyRule



因此，SecurityPolicy可以具有0，一个或多个SecurityPolicyRules。



我正在使用Julie Lerman的实体框架书来实现一定程度的并发检查，TDD和POCO支持。 / p>

我明白每个表应该有一个rowversion / timestamp字段，它被标记为ConcurrencyMode == Fixed。



我决定在存储过程中实现CUD。我的UPDATE Sproc如下：

  create PROCEDURE dbo.sp_M2_Core_UpdateSecurityPolicy 
 @ID int，
 @Name nvarchar（256）
 @Comment nvarchar（max）= null，
 @timestamp timestamp 
 AS 
 
 declare @nameExists nvarchar（256）
 
从M2_Core_SecurityPolicy中选择@ nameExists = [Name]，其中[Name] = @ Name和[ID]<> @id 
 if（not @nameExists为null）
 begin 
 raiserror（N'Name已在使用中：％s'，
 11，
 1，
 @Name）
 end 
 else 
 begin 
更新M2_Core_SecurityPolicy 
 set [Name] = @ Name，
 [Comment] = @ Comment 
其中id = @ id和[timestamp] = @ timestamp 
 IF @@ ROWCOUNT> ; 0 
 SELECT [Timestamp] AS newTimeStamp FROM M2_Core_SecurityPolicy WHERE id = @ id 
 end 
 
 go 
 
 create PROCEDURE dbo.sp_M2_Core_UpdateSecurityPolicyRule 
（
 @id int，
 @RoleName nvarchar（256），
 @Rank int，
 @CanReadExecute bit = null，
 @CanWrite bit = null ，
 @CanDelete bit = null，
 @CanExport bit = null，
 @Timestamp timestamp 
）
 
 AS 
 
声明@roleExists nvarchar（256）
声明@securityPolicyID int 
 
从vw_aspnet_Roles选择@ roleExists = [RoleName]其中[RoleName] = @ RoleName 
 if（@roleExists为null） 
 begin 
 raiserror（N'Role未定义：％s'，
 11，
 1，
 @roleName）
 end 
 else 
 begin 
从M2_Core_SecurityPolicyRule中选择@ securityPolicyID = [SecurityPolicyID]，其中[id] = @ id 
 
  - 移动所有其他规则优先级
 IF（SELECT COUNT（*）FROM M2_Core_SecurityPolicyRule WHERE [ID]<> @ID AND [SecurityPolicyID] = @ SecurityPolicyID AND [Rank] = @ Rank）> 0 
 BEGIN 
更新M2_Core_SecurityPolicyRule 
 SET [Rank] = [Rank] +1 
 WHERE [Rank]> = @rank 
 AND [SecurityPolicyID] = @ SecurityPolicyID 
 AND [ID]<> @ID 
 END 
 
更新M2_Core_SecurityPolicyRule 
 set [RoleName] = @ RoleName，
 [Rank] = @ Rank，
 [CanReadExecute] = @ CanReadExecute，
 [CanWrite] = @ CanWrite，
 [CanDelete] = @ CanDelete，
 [CanExport] = @ CanExport 
 where id = @ id和[timestamp] = @ timestamp 
 IF @@ ROWCOUNT> 0 
 SELECT [Timestamp] AS newTimeStamp FROM M2_Core_SecurityPolicyRule WHERE id = @ id 
 
 end 
 
返回
 
 go

我正在使用某些代码：


  1. 创建安全策略

  2. 将创建的安全策略规则添加到安全策略

  3. 添加安全策略

  4. 保存更新

  5. 将1添加到安全策略规则的排名

  6. 保存更新

测试如下：

  [TestMethod（）] 
 public void AddWithSecurityPolicyRuleChangeRankTest（）
 {
 ICoreContext coreContext = new CoreEntities（_coreDbConnectionString）; 
 CoreUnitOfWork coreUnitOfWork = new CoreUnitOfWork（coreContext）; 
 SecurityPolicyRepository target = new SecurityPolicyRepository（coreUnitOfWork）; 
 int originalCount = coreContext.SecurityPolicies.Count（）; 
 string securityPolicyName =addwithsecuritypolicyrulechangeruletest; 
 int originalRank = 1; 
 SecurityPolicy entity = new SecurityPolicy（）
 {
注释= null，
名称= securityPolicyName，
 SecurityPolicyRules =新的FixUpCollection< SecurityPolicyRule>（）
} ; 
 entity.SecurityPolicyRules.Add（
 new SecurityPolicyRule（）
 {
 CanDelete = null，
 CanExport = null，
 CanReadExecute = null，
 CanWrite = null，
 Rank = originalRank，
 RoleName =User
}）; 
 target.Add（entity）; 
 coreUnitOfWork.Save（）; 
 
 entity.SecurityPolicyRules [0] .Rank = originalRank + 1; 
 coreUnitOfWork.Save（）; //<  - 抛出异常
 SecurityPolicy savedSecurityPolicy = target.GetAll（）。Single（q => q.Name.Equals（securityPolicyName，StringComparison.CurrentCultureIgnoreCase））; 
 Assert.AreEqual（originalRank + 1，savedSecurityPolicy.SecurityPolicyRules [0] .Rank）; 
}

但是，当我运行它时，它会在突出显示的行中引发异常。例外情况是：


System.Data.OptimisticConcurrencyException
未被用户代码处理

Message = Store更新，插入或
删除语句影响
意外数量的行（0）。
实体可能已被修改或
删除，因为实体被加载。
刷新ObjectStateManager条目。

Source = System.Data.Entity

StackTrace：
在System.Data.Mapping.Update.Internal.UpdateTranslator.ValidateRowsAffected（ Int64
rowsAffected，UpdateCommand source）
在System.Data.Mapping.Update.Internal.UpdateTranslator.Update（IEntityStateManager
stateManager，IEntityAdapter适配器）
在System.Data.EntityClient。 EntityAdapter.Update（IEntityStateManager
entityCache）
在System.Data.Objects.ObjectContext.SaveChanges（SaveOptions
选项）
在System.Data.Objects.ObjectContext.SaveChanges（）
在MIGTurbo2.Core.Data.CoreEntities.Save（）

D：\dev\migturbo2.0\MIGTurbo2.Core\Data\Core.Context.cs：行
92
在MIGTurbo2.Repositories.CoreUnitOfWork.Save（）

D：\dev\migturbo2.0\MIGTurbo2.Repositories\CoreUnitOfWork.cs：行
26
在MIGTurbo2.Core.Tests.IntegrationTests.SecurityPolicyRepositoryTest.AddWithSecurityPolicyRuleChangeRankTest（）

D：\dev\migturbo2.0\MIGTurbo2.Core.Tests\IntegrationTests\SecurityPolicyRepositoryTest.cs：line
524 InnerException：


确实没有任何数据发生变化。即。 [Rank]从第一次更新仍然是1（因此是INSERT）。然而，通过SQL Profiler和Ayende的EF Profiler运行它，甚至没有调用数据库来进行UPDATE。所以时间戳/ rowversion的相关性肯定是无关紧要的？



可能是什么原因造成的？我不想每次保存刷新数据库！



更新1



运行应执行的SQL：

  declare @t timestamp 
从M2_Core_SecurityPolicyRule中选择@ t = [timestamp]，其中ID = 1 
 exec [sp_M2_Core_UpdateSecurityPolicyRule] @ id = 1，@ roleName ='User'，@ Rank = 2，@ Timestamp = @ t

它工作正常。在EF内发生阻塞电话的内容



更新2



通过突破代码，我发现发生以下情况：


  1. 项目被创建（显然，时间戳是零）

  2. 添加项目（Timestamp仍然为空）

  3. 更改已保存（这会导致INSERT）

  4. [Timestamp]字段然后从DB更新

  5. 因此，随后的UPDATE失败，如[Timestamp] IS NULL

那么为什么[Timestamp]字段不会更新？

解决方案

似乎我可能误解了朱莉·莱曼的书，或者她如何执行存储程序需要稍微改动。



我已经改变了模型和存储过程使得SProcs返回时间戳，并且模型选择它。因此，这意味着[Timestamp]字段不会为空。



所以INSERT SProc现在看起来像：

  create PROCEDURE dbo.sp_M2_Core_InsertSecurityPolicy 
 @Name nvarchar（256），
 @Comment nvarchar（max）= null 
 AS 
 
 declare @nameExists nvarchar（256）
 declare @id int 
 
从M2_Core_SecurityPolicy中选择@ nameExists = [Name]其中[Name] = @ Name 
 if（not @nameExists是null）
 begin 
 raiserror（N'Name已在使用中：％s'，
 11，
 1，
 @Name）
 end 
 else 
开始
 
插入到M2_Core_SecurityPolicy 
（[名称]，注释）
值
（@ Name，@ Comment）
 
 IF @@ ROWCOUNT> 0 
 BEGIN 
 SET @ id = SCOPE_IDENTITY（）
 SELECT @id as ID，[Timestamp] FROM M2_Core_SecurityPolicy WHERE ID = @ id 
 END 
 
结束
 
 go

，并且更改了Mapping，以便它拾起新字段：




I have a data-structure such that:

SecurityPolicy 1<---* SecurityPolicyRule

Therefore, a SecurityPolicy can have 0, one or many SecurityPolicyRules.

I am using Julie Lerman's Entity Framework book to implement some degree of concurrency checking, TDD and POCO support.

I understand that each table should have a rowversion/timestamp field, which is marked as ConcurrencyMode==Fixed.

I have decided to implement the CUD in Stored Procedures. My UPDATE Sproc is as follows:

create PROCEDURE dbo.sp_M2_Core_UpdateSecurityPolicy
    @ID int,
    @Name nvarchar(256),
    @Comment nvarchar(max)=null,
    @timestamp timestamp
AS

declare @nameExists nvarchar(256)

    select @nameExists= [Name] from M2_Core_SecurityPolicy where [Name]=@Name and [ID]<>@id
    if (not @nameExists is null)
    begin
        raiserror (N'Name is already in use: %s',
        11,
        1,
        @Name)
    end
    else
    begin
        update M2_Core_SecurityPolicy
            set [Name]=@Name,
                [Comment]=@Comment
                where id=@id and [timestamp]=@timestamp
        IF @@ROWCOUNT>0
            SELECT [Timestamp] AS newTimeStamp FROM M2_Core_SecurityPolicy WHERE id=@id
    end

go

create PROCEDURE dbo.sp_M2_Core_UpdateSecurityPolicyRule    
    (
    @id int,
    @RoleName nvarchar(256),
    @Rank int,
    @CanReadExecute bit=null,
    @CanWrite bit=null,
    @CanDelete bit=null,
    @CanExport bit=null,
    @Timestamp timestamp
    )

AS

    declare @roleExists nvarchar(256)
    declare @securityPolicyID int

    select @roleExists= [RoleName] from vw_aspnet_Roles where [RoleName]=@RoleName
    if (@roleExists is null)
    begin
        raiserror (N'Role is not defined: %s',
        11,
        1,
        @roleName)
    end
    else
    begin
        select @securityPolicyID=[SecurityPolicyID] from M2_Core_SecurityPolicyRule where [id]=@id

        -- move all other rules up in priority
        IF (SELECT COUNT(*) FROM M2_Core_SecurityPolicyRule WHERE [ID]<>@ID AND [SecurityPolicyID]=@SecurityPolicyID AND [Rank]=@Rank) > 0 
        BEGIN
            UPDATE M2_Core_SecurityPolicyRule
                SET [Rank]=[Rank]+1
                WHERE [Rank] >= @rank
                    AND [SecurityPolicyID]=@SecurityPolicyID
                    AND [ID]<>@ID
        END

        update M2_Core_SecurityPolicyRule
            set [RoleName]=@RoleName,
                [Rank]=@Rank,
                [CanReadExecute]=@CanReadExecute,
                [CanWrite]=@CanWrite,
                [CanDelete]=@CanDelete,
                [CanExport]=@CanExport              
                where id=@id and [timestamp]=@timestamp
        IF @@ROWCOUNT>0
            SELECT [Timestamp] AS newTimeStamp FROM M2_Core_SecurityPolicyRule WHERE id=@id

    end

    RETURN

go

I am testing this using some code that:

  1. Creates a Security Policy
  2. Adds a created Security Policy Rule to the Security Policy
  3. Adds the Security Policy
  4. Saves the updates
  5. Adds 1 to the Rank of the Security Policy Rule
  6. Saves the updates

The test is below:

[TestMethod()]
        public void AddWithSecurityPolicyRuleChangeRankTest()
        {
            ICoreContext coreContext = new CoreEntities(_coreDbConnectionString);
            CoreUnitOfWork coreUnitOfWork = new CoreUnitOfWork(coreContext);
            SecurityPolicyRepository target = new SecurityPolicyRepository(coreUnitOfWork);
            int originalCount = coreContext.SecurityPolicies.Count();
            string securityPolicyName = "addwithsecuritypolicyrulechangeruletest";
            int originalRank = 1;
            SecurityPolicy entity = new SecurityPolicy()
            {
                Comment = null,
                Name = securityPolicyName,
                SecurityPolicyRules = new FixUpCollection<SecurityPolicyRule>()
            };
            entity.SecurityPolicyRules.Add(
                new SecurityPolicyRule()
                {
                    CanDelete = null,
                    CanExport = null,
                    CanReadExecute = null,
                    CanWrite = null,
                    Rank = originalRank,
                    RoleName = "User"
                });
            target.Add(entity);
            coreUnitOfWork.Save();

            entity.SecurityPolicyRules[0].Rank=originalRank+1;
            coreUnitOfWork.Save(); // <-- exception thrown here
            SecurityPolicy savedSecurityPolicy = target.GetAll().Single(q => q.Name.Equals(securityPolicyName, StringComparison.CurrentCultureIgnoreCase));
            Assert.AreEqual(originalRank+1,savedSecurityPolicy.SecurityPolicyRules[0].Rank);
        }

However, when I run this, it throws an exception at the highlighted line. The exception is:

System.Data.OptimisticConcurrencyException was unhandled by user code
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. Refresh ObjectStateManager entries.
Source=System.Data.Entity
StackTrace: at System.Data.Mapping.Update.Internal.UpdateTranslator.ValidateRowsAffected(Int64 rowsAffected, UpdateCommand source) at System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter) at System.Data.EntityClient.EntityAdapter.Update(IEntityStateManager entityCache) at System.Data.Objects.ObjectContext.SaveChanges(SaveOptions options) at System.Data.Objects.ObjectContext.SaveChanges() at MIGTurbo2.Core.Data.CoreEntities.Save() in D:\dev\migturbo2.0\MIGTurbo2.Core\Data\Core.Context.cs:line 92 at MIGTurbo2.Repositories.CoreUnitOfWork.Save() in D:\dev\migturbo2.0\MIGTurbo2.Repositories\CoreUnitOfWork.cs:line 26 at MIGTurbo2.Core.Tests.IntegrationTests.SecurityPolicyRepositoryTest.AddWithSecurityPolicyRuleChangeRankTest() in D:\dev\migturbo2.0\MIGTurbo2.Core.Tests\IntegrationTests\SecurityPolicyRepositoryTest.cs:line 524 InnerException:

And sure enough, no data has changed. ie. The [Rank] is still 1 from the first update (therefore, the INSERT). However, running it through SQL Profiler and Ayende's EF Profiler, no calls to the database are even made to make the UPDATE. So the relevance of the timestamp/rowversion is surely ... irrelevant?

What could be causing this? I don't want to have to Refresh the DB on every Save!

Update 1

Having run the SQL that should execute:

declare @t timestamp
select @t=[timestamp] from M2_Core_SecurityPolicyRule where ID=1
exec [sp_M2_Core_UpdateSecurityPolicyRule] @id=1, @roleName='User',@Rank=2,@Timestamp=@t

It works fine. There is something inside EF occurring that is blocking the call

Update 2

By breaking through the code, I find that the following occurs:

  1. The item is created (obviously, Timestamp is null)
  2. The item is added (Timestamp still null)
  3. The changes are saved (this issues the INSERT)
  4. The [Timestamp] field is then NOT UPDATED from the DB
  5. Therefore, the subsequent UPDATE fails, as [Timestamp] IS NULL

So why would the [Timestamp] field not be updated?

解决方案

It seems that there I might have misunderstood Julie Lerman's book or there is a slight change required to how she implements her Stored Procedures.

I have changed the Model and Stored Procedures such that the SProcs return the Timestamp and the Model picks it up. This therefore means that the [Timestamp] field will not be null.

So the INSERT SProc now looks like:

create PROCEDURE dbo.sp_M2_Core_InsertSecurityPolicy
    @Name nvarchar(256),
    @Comment nvarchar(max)=null
AS

    declare @nameExists nvarchar(256)
    declare @id int 

    select @nameExists= [Name] from M2_Core_SecurityPolicy where [Name]=@Name
    if (not @nameExists is null)
    begin
        raiserror (N'Name is already in use: %s',
        11,
        1,
        @Name)
    end
    else
    begin

        INSERT INTO M2_Core_SecurityPolicy
            ([Name],Comment)
            values
            (@Name,@Comment)

        IF @@ROWCOUNT > 0 
        BEGIN
            SET @id=SCOPE_IDENTITY()
            SELECT @id as ID,[Timestamp] FROM M2_Core_SecurityPolicy WHERE ID=@id
        END

    end

go

and the Mapping is changed so that it picks up the "new" field:

这篇关于在UPDATE上使用Entity Framework的OptimisticConcurrencyException会影响0行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
.NET组件控件最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆