LINQ Insert Into Database resulted in duplicates

Question

I have a linq query running in a WCF Web Service that looks for a match and if one is not found then it creates one.

my code looks like

   //ReadCommitted transaction
   using (var ts = CreateTransactionScope(TransactionScopeOption.RequiresNew))
   {
    Contract contract = db.Contracts.SingleOrDefault(x => x.txtBlah == str);
    if (contract == null)
    {
      contract = new Contract();
      contract.txtBlah = str;
      db.Contracts.InsertOnSubmit(contract);
      db.SubmitChanges();
    }
    ...
    db.SubmitChanges();
   }

The problem is that I am getting duplicates. I thought the transaction would have locked the database to ensure no duplicates would be found (and supply the ability to rollback). How can I ensure that there are no duplicates?

Answer 1

In a ReadCommited transaction the data can be changed before the end of the transaction. But you can use Serializable transaction which will do table locking.

db.Connection.Open();
using (db.Transaction = db.Connection.BeginTransaction(IsolationLevel.Serializable))
{
//your code here
db.Transaction.Commit();
}

---

Ok, if you don't want to use Serializable tran then you should write a sproc for atomic insert, the logic should look like this SQL in code:

db.ExecuteCommand("INSERT INTO Contract (txtBlah) SELECT {0} WHERE NOT EXISTS (SELECT 1 FROM Contract WITH (TABLOCK) WHERE txtBlah={0})", str);

note this will also lock the whole table during the insert.

Read more on how to create a sproc without a race condition at http://weblogs.sqlteam.com/dang/archive/2007/10/28/Conditional-INSERTUPDATE-Race-Condition.aspx.