Graeme Hill's Dev Blog

A common pattern in database design is to use make a column required, give it a default value and then never think about it when doing INSERTs. A perfect example would be a createdDate column on the Users with a default value of GetDate(). Here's the full table definition:

• userID (identity key)
• userName
• password
• ts (timestamp)
• createdDate (default value = GetDate())

In this case we can easily insert into the table without worrying about the createdDate, userID, or ts columns:

INSERT INTO Users (userName, password) VALUES ('asdf', 'qwer')

However, since this is the 21st century, we don't want to do this in SQL, we want to do it with an ORM. Unfortunately, LINQ to SQL doesn't do a very good job with this.

Using context = New TestDataContext

    ' Output SQL to the console for debugging
    context.Log = Console.Out

    ' Attach a new user and submit the changes
    Dim newUser As New User With {.userName = "NewUser", .password = "password"}
    context.Users.InsertOnSubmit(newUser)
    context.SubmitChanges()

End Using

The above code generates the following INSERT statement when SubmitChanges() is called (note: I replaced @p0, @p1, etc with their actual values to make the query more readable):

INSERT INTO [dbo].[Users]([userName], [password], [createdDate])
VALUES ('NewUser', 'password', NULL)

This query fails and we get a SqlTypeException because createdDate is NOT NULL and NULL cannot be converted to a valid date. Notice that the generated SQL does not attempt to explicitly set a value for userID or ts. It appears that LINQ to SQL knows how to deal with IDENTITY fields and TIMESTAMPs, but not how to deal with other required columns that happen to have a default value.

I would have expected LINQ to SQL to generate a query that does not explicitly set createdDate so that SQL Server could handle it, but no such luck. You can easily set the createdDate manually like this:

Dim newUser As New User With {
  .userName = "NewUser",
  .password = "password",
  .createdDate = Date.Now }

It really sucks to have to do this every time though, especially if you have many fields to fill in. A possible alternative is to put a partial class on either your DataContext or just on the User class and write some code that will automatically initialize fields like createdDate. If you want to make generic behaviour for this (eg: automatically set columns named "createdDate" to Date.Now when SubmitChanges is called) you can do something like this in the DataContext partial class:

Public Overrides Sub SubmitChanges(ByVal failureMode As ConflictMode)

    ' NOTE: this is just a sample to get you started
    For Each insert In GetChangeSet().Inserts
        Dim createdDateProp = insert.GetType.GetProperty("createdDate")
        If createdDateProp IsNot Nothing Then
            createdDateProp.SetValue(insert, Date.Now, Nothing)
        End If
    Next

    MyBase.SubmitChanges(failureMode)

End Sub