LINQ to SQL Transactions

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();
    }
}

LINQ basic group (left outer) join

The left outer join is my favourite join, so it’s worth giving it a post of its own…
VB.NET

Dim groupJoinResults = From instA In Me.LINQ2ClassAList _
                       Group Join instB In Me.LINQ2ClassBList _
                       On instA.Region Equals instB.Region _
                       Into groupedBs = Group _
                       Select instA, groupedBs

C#

var groupJoinResults = from instA in this.LINQ2ClassAList
                       join instB in this.LINQ2ClassBList
                       on instA.Region equals instB.Region
                       into groupedBs
                       select new { instA, groupedBs };

In this example the groupedBs are accessible via (result).groupedBs and are themselves var/anonymous types.
groupJoinResults.First().instA.Name = the Name of the first instA returned and
groupJoinResults.First().groupedBs.First().Name = the Name of the first instB that matched it.

In my words a Group Join is a Left Outer Join with the results of the right dumped into an IEnumerable.

LINQ inner join example

Just a quick basic example of a LINQ inner join returning an anonymous type

VB.NET

Dim joinedResults = From instA In Me.LINQ2ClassAList _
                    Join instB In Me.LINQ2ClassBList _
                    On instA.Region Equals instB.Region _
                    Select AName = instA.Name, BName = instB.Name, instA.Region

C#

var joinedResults = from instA in this.LINQ2ClassAList
                    join instB in this.LINQ2ClassBList
                    on instA.Region equals instB.Region
                    select new { AName = instA.Name, BName = instB.Name, instA.Region };