CODEWITHSUNDEEP
c#sql-serverlinqlinq-to-sqltransactions
Dan
Dan

Reputation: 45752

Does LINQ2SQL automatically put ExecuteCommand in a transaction

Does the documentation quotation from this answer: https://stackoverflow.com/a/542691/1011724

When you call SubmitChanges, LINQ to SQL checks to see whether the call is in the scope of a Transaction or if the Transaction property (IDbTransaction) is set to a user-started local transaction. If it finds neither transaction, LINQ to SQL starts a local transaction (IDbTransaction) and uses it to execute the generated SQL commands. When all SQL commands have been successfully completed, LINQ to SQL commits the local transaction and returns.

apply to the .ExecuteCommand() method? In otherwords, can I trust that the following delete is handled in a transaction and will automatically rollback if it fails or do I need to manually tell it to use a transaction and if so how? Should I use TransactionScope?

using(var context = Domain.Instance.GetContext())
{
    context.ExecuteCommand("DELETE FROM MyTable WHERE MyDateField = {0}", myDate)
}

Upvotes: 0

Views: 489

Answers (1)

Jeroen Mostert
Jeroen Mostert

Reputation: 28789

Every SQL statement, whether or not wrapped in an explicit transaction, occurs transactionally. So, explicit transaction or not, individual statements are always atomic -- they either happen entirely or not at all. In the example above, either all rows that match the criterion are deleted or none of them are -- this is irrespective of what client code does. There is literally no way to get SQL Server to delete the rows partially; even yanking out the power cord will simply mean whatever was already done for the delete will be undone when the server restarts and reads the transaction log.

The only fly in the ointment is that which rows match can vary depending on how the statement locks. The statement logically happens in two phases, the first to determine which rows will be deleted and the second to actually delete them (while under an update lock). If you, say, issued this statement, and while it was running issued an INSERT that inserted a row matching the DELETE criterion, whether the row is in the database or not after the DELETE has finished depends on which transaction isolation level was in effect for the statements. So if you want practical guarantees about "all rows" being deleted, what client code does comes into scope. This goes a little beyond the scope of the original question, though.

Upvotes: 2

Related Questions