Graeme Hill on .NET development

There are a few problems with the data model code that is automatically generated in LINQ to SQL. The most obvious issues are that class names are not capitalized and that tables with two foreign keys to the same table will not have descriptive names. For example, if a table has columns firstUserID and secondUserID which are both foreign keys to the users table then you would probably hope to see the properties FirstUser and SecondUser on that class. Unfortunately, what you will actually get is User and User1 which is pretty much pointless because it is very difficult to tell which one is which. One way to tackle this is to simply change the code after it has been generated so that it looks like you want it to. But then as soon as you change the database and import the model again your changes are lost and have to be redone every time. Ideally, the automatically generated code would be formatted exactly as you want it.

The solution I came up with was to use SqlMetal to generate XML output, then manipulate that output and feed it back into SqlMetal so that it can generate the code from the altered XML. As a naming heuristic, I specified that a foreign key column’s property would have a name derived from the column name without the “ID” suffix and a capitalized first letter. Depending on the extent of the changes you plan on making, you may want to make an XSLT file to translate the XML, or simply use some regular expressions.

One downside to this approach is that SqlMetal often cannot generate code for stored procedures because it is unable to determine the return type without actually running the procedure. To get around this I told SqlMetal not to generate any code for the stored procedures (just omit the /sprocs argument), then manually incorporated the XML for my stored procedures in the XSLT file so that it would be injected into the second input to SqlMetal that actually generates the code.

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