Precedence of SQL queries in Linq-2-SQL Context

I have a table that holds Key (LocumID) - Value (AvailableDate) pairs. The table has it's own primer. The key/value cannot be repeated so I added a unique composite constraint on LocumID & AvailableDate.

OID                  LocumID              AvailableDate           AvailabilityStatusID 
-------------------- -------------------- ----------------------- -------------------- 
1                    1                    2009-03-02 00:00:00     1                    
2                    2                    2009-03-04 00:00:00     1                    
3                    1                    2009-03-05 00:00:00     1                    
4                    1                    2009-03-06 00:00:00     1                    
5                    2                    2009-03-07 00:00:00     1                    
6                    7                    2009-03-09 00:00:00     1                    
7                    1                    2009-03-11 00:00:00     1                    
8                    1                    2009-03-12 00:00:00     2                    
9                    1                    2009-03-14 00:00:00     1                    
10                   1                    2009-03-16 00:00:00     1                    

Now, upon real usage, I find that simply eliminating old value(s) for a LocumID-AvailableDate pair and insert a new one is best approach. However, in Linq-to-SQL, I call DbContext.SubmitChanges() in the end and that's where the SQL Server throws an exception complaining that the statement conflicted with the constraint.

Now, i开发者_如何学运维f I check my code-lines, I'm deleting pre-existing pair first, creating new and inserting it. Why is the constraint being violated in this transaction?

Also, if I delete the pre-existing pair first, then SubmitChanges and then proceed to create a new one, insert it, and SubmitChanges... all is well.

However, I lose the benefit of a complete transaction.

Any ideas? Regards.

SubmitChanges doesn't guarente the calls to be made in that order. However, instead of relying on linq to sql to make a transaction, simply make one yourself:

using(TransactionScope scope = new TransactionScope())
{
  //first call
  context.SubmitChanges();

  //other work
  context.SubmitChanges();
}

linq to sql will first check if it finds a transaction before making one on its own.