title: Optimistic Concurrency using Stored Procedures in Entity Framework
body: <p>In the entity framework, using stored procedures, I would like to perform an update on my table using optimistic concurrency. I've been unable to get this working, without modifying my existing update stored procedure. I'm trying to determine if there is a way I can map my existing stored procedure so that a concurrency exception will occur when no rows are updated.</p>
  
  <p>Some background information: </p>
  
  <ul>
  <li>I've mapped the update stored procedure (including the timestamp) column in my .edmx file</li>
  <li><p>My existing stored procedure looks like the following (the actual table names and columns are obviously omitted):</p>
  
  <pre><code>UPDATE Table SET Column = @Column1, Column2 = @Column2 ....
  WHERE PK = @PK AND Timestamp = @Timestamp
  
  SELECT PK, Column1, Column2, ....., Timestamp
  FROM Table WHERE PK = @PK
  </code></pre></li>
  <li><p>In the event that the update fails (due to a timestamp mismatch) the select portion of the stored procedure <em>will</em> still return a row.</p></li>
  </ul>
  
  <p>When I modify the stored procedure to the following: </p>
  
  <pre><code>   UPDATE Table SET Column = @Column1, Column2 = @Column2 ....
     WHERE PK = @PK AND Timestamp = @Timestamp
  
     IF @@ROWCOUNT &gt; 0
         SELECT PK, Column1, Column2, ....., Timestamp
         FROM Table WHERE PK = @PK
  </code></pre>
  
  <p>Then everything works as expected, and the concurrency error occurs.</p>
  
  <p>Alternatively if I make the stored procedure return an output parameter, and map that output parameter to the "Rows Affected Parameter" in the Entity Framework's .edmx file, then the concurrency error also works as expected.</p>
  
  <p>This solution (i.e. using the output parameter) is explained best, I found, here:
  <a href="http://petermannerhult.wordpress.com/2010/10/01/entity-framework-4-with-optimistic-concurrency-and-stored-procedures/" rel="noreferrer">http://petermannerhult.wordpress.com/2010/10/01/entity-framework-4-with-optimistic-concurrency-and-stored-procedures/</a></p>
  
  <p>Neither of the above steps seem like they should be necessary though, as I would assume that the Entity Framework could just use the number of rows updated to determine if it should raise a concurrency exception. I've used these exact same stored procedures (with optimistic concurrency) in ADO.NET DataSets, without any problem. So my question is how can I use my existing stored procedures, without modification, to enable optimistic concurrency in the Entity Framework?</p>