OptimisticConcurrencyException with Entity Framework on an UPDATE, affects 0 rows

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 1678498408 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 1678498408 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 wo开发者_如何转开发rks 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 1678498408 field is then NOT UPDATED from the DB
5. Therefore, the subsequent UPDATE fails, as 1678498408 IS NULL

So why would the 1678498408 field not be updated?

Generally speaking, this is because the timestamp of the entity in the objectstatemanager no longer matches what is in the DB.

Call coreContext.Refresh(RefreshOptions.StoreWins (or .ClientWins depending on what you want),entity);

to sync the entity and the DB before calling the save.

For a good post explaining optimistic concurrency see http://social.msdn.microsoft.com/Forums/en/adodotnetentityframework/thread/457f2196-dd21-4188-8185-2561b954c54b or http://msdn.microsoft.com/en-us/library/bb738618.aspx

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 1678498408 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,1678498408 FROM M2_Core_SecurityPolicy WHERE ID=@id
        END

    end

go

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