Graeme Hill's Dev Blog

Discard changes in LINQ to SQL DataContext

Star date: 2009.040

The LINQ to SQL DataContext provides excellent functionality for managing a set of local changes to a database that can be pushed to the server with a single call to SubmitChanges(). Inevitably there will be situations where you want to discard the changes you have made, effectively allowing you to continue using the DataContext as though those changes had never been made. Unfortunately, there is no DataContext.DiscardChanges() method.

A little research reveals that this is by design and that you should simply recreate the DataContext in these cases, but of course, nothing is that simple. Every object you have that came from the original DataContext now needs to be reset to use the new one to guarantee predictable behaviour. For example, the last line of this snippet will set objectsAreEqual to False:

' Create two data contexts
Dim dc1 As New UsersDataContext
Dim dc2 As New UsersDataContext

' Grab the same record out of both the data contexts
Dim user1 = dc1.Users.First
Dim user2 = dc2.Users.First

' False, because the otherwise identical records come from different contexts
Dim objectsAreEqual = user1 = user2

Basically, this shows that it does matter where your object came from. If you just want to discard a few little changes and you don't want to have to recreate all your variables, you can instead undo all the changes that you have made so far and continue to use the same DataContext.

Undoing inserts and deletes is easy, because we can just do the opposite to revert the change. Luckily there is even a nice function on the DataContext that will show us what we need to do called GetChangeSet:

Public Sub DiscardInsertsAndDeletes(ByVal data As DataContext)
    ' Get the changes
    Dim changes = data.GetChangeSet()

    ' Delete the insertions
    For Each insertion In changes.Inserts
        data.GetTable(insertion.GetType).DeleteOnSubmit(insertion)
    Next

    ' Insert the deletions
    For Each deletion In changes.Deletes
        data.GetTable(deletion.GetType).InsertOnSubmit(deletion)
    Next
End Sub

LINQ to SQL is smart enough to know that if a row is inserted and then deleted (or the other way around) nothing needs to be done. If you look at the ChangeSet after running this function, you will notice that the Inserts and Deletes collections are empty. Updates are a little more annoying. If you update a value, and then set it back to its original state then you will get the expected behaviour (ie: the ChangeSet will be empty because you reverted your change) but unless you write code to keep track of the original value yourself, there is no way to automatically put an object back into its start state without hitting the database. If hitting the database is acceptable, you can always use the Refresh function to get rid of the updates:

Public Sub DiscardUpdates(ByVal data As DataContext)
    ' Get the changes
    Dim changes = data.GetChangeSet()

    ' Refresh the tables with updates
    Dim updatedTables As New List(Of ITable)
    For Each update In changes.Updates
        Dim tbl = data.GetTable(update.GetType)
        ' Make sure not to refresh the same table twice
        If updatedTables.Contains(tbl) Then
            Continue For
        Else
            updatedTables.Add(tbl)
            data.Refresh(RefreshMode.OverwriteCurrentValues, tbl)
        End If
    Next
End Sub