1

Closed

Stored Procs :: Concurrency check on value types in conjunction with stored procedures doesn't work properly

description

Consider the following model:
    public class Foo
    {
        public int Id { get; set; }

        [ConcurrencyCheck]
        public DateTime BirthDate { get; set; }
    }

    public class MyContext : DbContext
    {
        public DbSet<Foo> Foos { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Foo>().MapToStoredProcedures();
        }
    }
Enable-Migrations
Add-Migration Mig1

will generate the following:
            CreateTable(
                "dbo.Foos",
                c => new
                    {
                        Id = c.Int(nullable: false, identity: true),
                        BirthDate = c.DateTime(nullable: false),
                    })
                .PrimaryKey(t => t.Id);
            
            CreateStoredProcedure(
                "dbo.Foo_Insert",
                p => new
                    {
                        BirthDate = p.DateTime(),
                    },
                body:
                    @"INSERT [dbo].[Foos]([BirthDate])
                      VALUES (@BirthDate)
                      
                      DECLARE @Id int
                      SELECT @Id = [Id]
                      FROM [dbo].[Foos]
                      WHERE @@ROWCOUNT > 0 AND [Id] = scope_identity()
                      
                      SELECT t0.[Id]
                      FROM [dbo].[Foos] AS t0
                      WHERE @@ROWCOUNT > 0 AND t0.[Id] = @Id"
            );
            
            CreateStoredProcedure(
                "dbo.Foo_Update",
                p => new
                    {
                        Id = p.Int(),
                        BirthDate = p.DateTime(),
                        BirthDate_Original = p.DateTime(),
                    },
                body:
                    @"UPDATE [dbo].[Foos]
                      SET [BirthDate] = @BirthDate
                      WHERE (([Id] = @Id) AND ([BirthDate] = @BirthDate))"
            );
            
            CreateStoredProcedure(
                "dbo.Foo_Delete",
                p => new
                    {
                        Id = p.Int(),
                        BirthDate_Original = p.DateTime(),
                    },
                body:
                    @"DELETE [dbo].[Foos]
                      WHERE (([Id] = @Id) AND ([BirthDate] = @BirthDate_Original))"
            );
Note that for Update proc, we compare against @BirthDate rather than BirthDate_Original. This will result in concurrency exception being thrown when it shouldn't have, if one updates the concurrency check value.

The following code:
            using (var ctx = new MyContext())
            {
                var foo = new Foo
                {
                    BirthDate = new DateTime(2000, 1, 1),
                };

                ctx.Foos.Add(foo);
                ctx.SaveChanges();
                foo.BirthDate = DateTime.Now;
                ctx.SaveChanges();
            }
throws:

Unhandled Exception: System.Data.Entity.Infrastructure.DbUpdateConcurrencyException: 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. ---> System.Data.Entity.Core.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.
at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.ValidateRowsAffected(Int64 rowsAffected, UpdateCommand source)
at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update()
at System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.<Update>b__2(UpdateTranslator ut)
at System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.Update[T](T noChangesResult, Func2 updateFunction, Boolean throwOnClosedConnection)
at System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.Update(Boolean throwOnClosedConnection)
at System.Data.Entity.Core.Objects.ObjectContext.<SaveChangesToStore>b__33()
at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func
1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesToStore(SaveOptions options, IDbExecutionStrategy executionStrategy)
at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass28.<SaveChanges>b__25()
at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
at System.Data.Entity.Core.Objects.ObjectContext.SaveChanges(SaveOptions options)
at System.Data.Entity.Internal.InternalContext.SaveChanges()
--- End of inner exception stack trace ---
at System.Data.Entity.Internal.InternalContext.SaveChanges()
at System.Data.Entity.Internal.LazyInternalContext.SaveChanges()
at System.Data.Entity.DbContext.SaveChanges()
Closed Nov 8, 2013 at 6:48 PM by maumar
verified, closing

comments

maumar wrote Sep 10, 2013 at 9:32 PM

Note: this works for scenarios without stored procedures

ajcvickers wrote Nov 6, 2013 at 10:08 PM

Fixed in changeset f5f422b9951ed317b4050e3fa41308859d519c95