The thing about Transactions in LINQ is that they are rubbish.
It’s all designed around submitting changes to a transaction, great, but if you rollback the transaction those changes can not be submitted a second time, you’d have to manually undo the changes or recreate the DataContext and apply the changes once more.
Below are some examples I put together whilst trying out LINQ to SQL, as they are copied from a Wiki I used to have, please let me know if you spot any errors.
Here’s an example of this with an insert.
VB.NET
Dim testerString As String = "HELLO" Dim meeee As LINQtoSQLDataClassesDataContext = Nothing Try ' New DataContext meeee = New LINQtoSQLDataClassesDataContext() With {.Log = Console.Out} ' Open connection meeee.Connection.Open() ' New transaction meeee.Transaction = meeee.Connection.BeginTransaction() ' Insert command meeee.TableAs.InsertOnSubmit(New TableA() With {.Name = testerString, .Region = "East"}) ' Submit changes (to the transaction) meeee.SubmitChanges() ' Rollback meeee.Transaction.Rollback() ' This would fail on submit because the in memory would have a duplicate - which is annoying but kind of makes sense 'meeee.Transaction = meeee.Connection.BeginTransaction() 'meeee.TableAs.InsertOnSubmit(New TableA() With {.Name = testerString, .Region = "West"}) 'meeee.SubmitChanges() 'meeee.Transaction.Commit() ' This would have no effect - changes already submitted, so there are none 'meeee.Transaction = meeee.Connection.BeginTransaction() 'meeee.SubmitChanges() 'meeee.Transaction.Commit() ' This would have no effect - once submitted there doesn't appear to be any way back 'meeee.Transaction = meeee.Connection.BeginTransaction() 'meeee.Refresh(--of any kind--) 'meeee.SubmitChanges() 'meeee.Transaction.Commit() Finally If (Not meeee Is Nothing) Then If (Not meeee.Connection Is Nothing) Then meeee.Connection.Close() End If meeee.Dispose() End If End Try
C#
String testerString = "HELLO"; LINQtoSQLDataClassesDataContext meeee = null; try { // New DataContext meeee = new LINQtoSQLDataClassesDataContext() { Log = Console.Out }; // Open connection meeee.Connection.Open(); // New transaction meeee.Transaction = meeee.Connection.BeginTransaction(); // Insert command meeee.TableAs.InsertOnSubmit(new TableA() { Name = testerString, Region = "East" }); // Submit changes (to the transaction) meeee.SubmitChanges(); // Rollback meeee.Transaction.Rollback(); // This would fail on submit because the in memory would have a duplicate - which is annoying but kind of makes sense //meeee.Transaction = meeee.Connection.BeginTransaction(); //meeee.TableAs.InsertOnSubmit(new TableA() { Name = testerString, Region = "West" }); //meeee.SubmitChanges(); //meeee.Transaction.Commit(); // This would have no effect - changes already submitted, so there are none //meeee.Transaction = meeee.Connection.BeginTransaction(); //meeee.SubmitChanges(); //meeee.Transaction.Commit(); // This would have no effect - once submitted there doesn't appear to be any way back //meeee.Transaction = meeee.Connection.BeginTransaction(); //meeee.Refresh(--of any kind--); //meeee.SubmitChanges(); //meeee.Transaction.Commit(); } finally { if (null != meeee) { if (null != meeee.Connection) { meeee.Connection.Close(); } meeee.Dispose(); } }
This next part is the retry i.e. ditch it and redo
VB.NET
' Start a clean DataContext if you want to retry / redo the updates Dim meee2 As LINQtoSQLDataClassesDataContext = Nothing Try meee2 = New LINQtoSQLDataClassesDataContext() With {.Log = Console.Out} meee2.Connection.Open() meee2.Transaction = meee2.Connection.BeginTransaction() meee2.TableAs.InsertOnSubmit(New TableA() With {.Name = testerString, .Region = "South"}) meee2.SubmitChanges() meee2.Transaction.Commit() Finally If (Not meee2 Is Nothing) Then If (Not meee2.Connection Is Nothing) Then meee2.Connection.Close() End If meee2.Dispose() End If End Try
C#
// Start a clean DataContext if you want to retry redo the updates LINQtoSQLDataClassesDataContext meee2 = null; try { meee2 = new LINQtoSQLDataClassesDataContext() { Log = Console.Out }; meee2.Connection.Open(); meee2.Transaction = meee2.Connection.BeginTransaction(); meee2.TableAs.InsertOnSubmit(new TableA() { Name = testerString, Region = "South" }); meee2.SubmitChanges(); meee2.Transaction.Commit(); } finally { if (null != meee2) { if (null != meee2.Connection) { meee2.Connection.Close(); } meee2.Dispose(); } }
Now is an example of a delete:
VB.NET
' Delete it Dim meee3 As LINQtoSQLDataClassesDataContext = Nothing Try meee3 = New LINQtoSQLDataClassesDataContext() With {.Log = Console.Out} meee3.Connection.Open() meee3.Transaction = meee3.Connection.BeginTransaction() meee3.TableAs.DeleteOnSubmit( _ meee3.TableAs.Where(Function(x) x.Name = testerString).Select(Function(x) x).FirstOrDefault()) meee3.SubmitChanges() meee3.Transaction.Commit() Finally If (Not meee3 Is Nothing) Then If (Not meee3.Connection Is Nothing) Then meee3.Connection.Close() End If meee3.Dispose() End If End Try
C#
// Delete it LINQtoSQLDataClassesDataContext meee3 = null; try { meee3 = new LINQtoSQLDataClassesDataContext() { Log = Console.Out }; meee3.Connection.Open(); meee3.Transaction = meee3.Connection.BeginTransaction(); meee3.TableAs.DeleteOnSubmit( meee3.TableAs.Where(x => x.Name == testerString).Select(x => x).FirstOrDefault()); meee3.SubmitChanges(); meee3.Transaction.Commit(); } finally { if (null != meee3) { if (null != meee3.Connection) { meee3.Connection.Close(); } meee3.Dispose(); } }