Precedence of SQL queries in Linq-2-SQL Context

Question

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, if 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.

Have you set the .Log property on the DataContext so that you can see the offending SQL? It might help to post that as well. — mattmc3
I don't know why you are asking those questions. The problem is crystal clear: Linq2Sql performs the insert before the delete. — Daniel Hilgarth
Ok: "Linq2Sql performs the insert before the delete". Is that a documented behavior or is it just happening to me. I do understand that the order of query to be executed cannot be determined beforehand nor it is guaranteed. — Hassan Gulzar

Femaref Femaref · Accepted Answer · 2011-07-12T11:25:27

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.

Precedence of SQL queries in Linq-2-SQL Context

1 Answers