在UPDATE上使用Entity Framework的OptimisticConcurrencyException会影响0行 [英] OptimisticConcurrencyException with Entity Framework on an UPDATE, affects 0 rows
问题描述
我有一个数据结构:
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添加到安全策略规则的排名
- 保存更新
测试如下:
[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
通过突破代码,我发现发生以下情况:
- 项目被创建(显然,时间戳是零)
- 添加项目(Timestamp仍然为空)
- 更改已保存(这会导致INSERT)
- [Timestamp]字段然后从DB更新
- 因此,随后的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:
- Creates a Security Policy
- Adds a created Security Policy Rule to the Security Policy
- Adds the Security Policy
- Saves the updates
- Adds 1 to the Rank of the Security Policy Rule
- 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:
- The item is created (obviously, Timestamp is null)
- The item is added (Timestamp still null)
- The changes are saved (this issues the INSERT)
- The [Timestamp] field is then NOT UPDATED from the DB
- 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屋!