Databases
StackOverflow2013
Postsdbo
POHow do I get Optimistic Concurrency to fail with detached objects in EF?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow do I get Optimistic Concurrency to fail with detached objects in EF?
    primarykey
    data
    text
    <p>Our project is currently migrating to EF(and away from Stored Procs), and one of the enhancements(we're adding to the architecture) is using Optimistic Concurrency when users save data to the database(we currently don't have this feature). I'm having problems getting EF to fail when it should. In other words when two users open the same record, each make changes and attempts to save those changes the first to save update the record, and the second would get an error message. I created a simple example to illustrate my problem.</p> <p>In the database I have the following table(and insert test data):</p> <pre><code>Create Table Work ( Id int identity(1,1) Primary Key ,UserIdAssignTo int null ,RowVer RowVersion not null ) Insert Into Work(UserIdAssignTo)Values(1) </code></pre> <p>I created an EF file (.edmx) and drag/drop the table, above, onto the canvas. I updated the properties on the property/column RowVer as follows:</p> <ul> <li>RowVer Property/Column</li> <li>Concurrency Mode: Fixed</li> <li>Getter/Setter are both Public</li> <li>Nullable: False</li> <li>Store Generated: Computed</li> <li>Type: Binary</li> </ul> <p>I have an object that will retrieve and update the table like below:</p> <pre><code>public class Work { public int Id { get; set; } public int? UserIdAssignTo { get; set; } public byte[] Version { get; set; } private string _conn = String.Empty; public WorkData() { _conn = GetConnectionsString(); } public void GetById(int WorkID) { using (SQL context = new SQL(_conn)) { Work fromDb = context.Works.FirstOrDefault(db =&gt; db.Id == WorkID); if (fromDb != null) { Id = fromDb.Id; UserIdAssignTo = fromDb.UserIdAssignTo; Version = fromDb.RowVer; } } } public void Update() { using (SQL context = new SQL(_conn)) { Work fromDb = context.Works.FirstOrDefault(db =&gt; db.Id == Id); if (fromDb != null) { fromDb.UserIdAssignTo = UserIdAssignTo; fromDb.RowVer = Version; context.SaveChanges(); UserIdAssignTo = fromDb.UserIdAssignTo; Version = fromDb.RowVer; } } } } </code></pre> <p>I developed a test case to expose the error I'm getting:</p> <pre><code>[Test] public void ConcurencyDataTest() { WorkData first = new WorkData(); first.GetById(1); WorkData second = new WorkData(); second.GetById(1); first.UserIdAssignTo = null; first.Update(); second.UserIdAssignTo = 1; second.Update(); // I should get an exception b/c the object is outdated } </code></pre> <p>After both "first" and "second" object call the GetById(1) method, their RowVer property is the same for both objects(as expected).</p> <p>I ran SQL profiler when I executed this test</p> <p>The below is when the "first" object called Update method</p> <pre><code>exec sp_executesql N'update [dbo].[Work] set [UserIdAssignTo] = null where (([Id] = @0) and ([RowVer] = @1)) select [RowVer] from [dbo].[Work] where @@ROWCOUNT &gt; 0 and [Id] = @0',N'@0 int,@1 binary(8)',@0=1,@1=0x00000000024E6E2 </code></pre> <p>Note the @1 parameter, both the "first" and "second" object should have that in memory and use it when update</p> <p>When second.Update was called, the SQL profiler recorded this:</p> <pre><code>exec sp_executesql N'update [dbo].[Work] set [UserIdAssignTo] = @0 where (([Id] = @1) and ([RowVer] = @2)) select [RowVer] from [dbo].[Work] where @@ROWCOUNT &gt; 0 and [Id] = @1',N'@0 int,@1 int,@2 binary(8)',@0=1,@1=1,@2=0x00000000024E6E2F </code></pre> <p>Note the @1 parameter has changed to the new value(after "first" updated), when it should be the old value that was held by the object "second"(the old value is 0x00000000024E6E2). I don't understand how it got changed and I'm a little confused on how to properly implement first write concurrency through EF. </p> <p>The results I'm actually getting is the "second" object is successfully updating the table, when it should be failing.</p> <p>Edit: This to simulate using an N-tier architecture. I'm trying to update with detached objects.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USskaffman
    1. USChris L
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow do I get Optimistic Concurrency to fail with detached objects in EF?
      1. This table or related slice is empty.
      1. VTDownMod
    2. VO
      singulars
      1. POHow do I get Optimistic Concurrency to fail with detached objects in EF?
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload