title: Preventing race condition of if-exists-update-else-insert in Entity Framework
body: <p>I've been reading other questions on how to implement if-exists-insert-else-update semantics in EF, but either I'm not understanding how the answers work, or they are in fact not addressing the issue.  A common solution offered is to wrap the work in a transaction scope (eg: <a href="https://stackoverflow.com/questions/4189954/implementing-if-not-exists-insert-using-entity-framework-without-race-conditions">Implementing if-not-exists-insert using Entity Framework without race conditions</a>):</p>
  
  <pre><code>using (var scope = new TransactionScope()) // default isolation level is serializable
  using(var context = new MyEntities())
  {
      var user = context.Users.SingleOrDefault(u =&gt; u.Id == userId); // *
      if (user != null)
      {
          // update the user
          user.property = newProperty;
          context.SaveChanges();
      }
      else
      {
          user = new User
          {
               // etc
          };
          context.Users.AddObject(user);
          context.SaveChanges();
      }
  }
  </code></pre>
  
  <p>But I fail to see how this solves anything, as for this to work, the line I have starred above should <em>block</em> if a second thread tries to access the same user ID, unblocking only when the first thread has finished its work.  Using a transaction will <em>not</em> cause this however, and we'll get an UpdateException thrown due to the key violation that occurs when the second thread attempts to create the same user for a second time.</p>
  
  <p>Instead of catching the exception caused by the race condition, it would be better to prevent the race condition from happening in the first place.  One way to do this would be for the starred line to take out an exclusive lock on the database row that matches its condition, meaning that in the context of this block, only one thread at a time could work with a user.</p>
  
  <p>It seems that this must be a common problem for users of the EF, so I'm looking for a clean, generic solution that I can use everywhere.</p>
  
  <p>I'd really like to avoid using a stored procedure to create my user if possible.</p>
  
  <p>Any ideas?</p>
  
  <p><strong>EDIT</strong>: I tried executing the above code concurrently on two different threads using the same user ID, and despite taking out serializable transactions, they were both able to enter the critical section (*) concurrently. This lead to an UpdateException being thrown when the second thread attempted to insert the same user ID that the first had just inserted. This is because, as pointed out by Ladislav below, a serializable transaction takes exclusive locks only after it has begun modifying data, not reading.</p>