It’s Not My DeFault
LINQ to SQL does not support SQL default values on columns. For example, many of my tables have a "CreatedDate" which has the SQL default value of GetDate(). The column does not allow NULL values, so the LINQ to SQL class is DateTime, rather than DateTime?
The result of this is that since LINQ to SQL does not know about default values, it tries to insert the value of the underlying field even if it’s not been set – for example a DateTime that is essentially "zero" (DateTime.MinValue). This will result in a SQL error since SQL does not support a "zero" value date.
Modifying the DBML
One solution suggested here is that the columns with defaults are set to IsDbGenerated = "true". This means that LINQ to SQL will not attempt to insert a value from an object, and will always use the database value. This works – but it also means that the field is now effectively read-only as well, as any attempt to edit it will result in a LINQ error.
A second problem with this solution is that if you refresh your model from the database this manual setting (I have some code that does this) it will be overwritten. This makes keeping the setting and code stable a problem.
OnCreated solution
I used to think the alternative approach I found was suitable, which was to use the extensibility method OnCreated in any object with a default. You simply set the default value here so that any new instance would have it set, e.g.
Private Sub OnCreated()
Me.CreatedDate = Now
End Sub
This sets the value when a new object is created as LINQ calls OnCreated from the constructor. And when LINQ loads an object from the database the .CreatedDate is overwritten with the actual value.
That seemed to work quite well but I came across a problem. I had a form databound to a LINQdataSource control. The CreatedDate value was not in the form, so in theory should be untouched. However, when LINQdataSource does an update, it creates an empty instance of your object, then copies the bound properties from the form into this object. It then compares the original object (loaded from the database) with this new object, and modifies the values of that, then updates.
In doing this it thinks that CreatedDate has been modified since it has been changed – and so the "default" field values get modified unintentionally.
Insert<Entity> Solution
The solution I eventually found was to use the Insert{Entity} extensibility method which lives in the DataContext. This is called by LINQ to SQL just before any Insert which is caused whenever .SubmitChanges is called. So for a class Customer we have
Private Sub InsertCustomer(instance as Customer)
If instance.CreatedDate = DateTime.MinValue Then _
instance.CreatedDate = Now
‘tell datacontext to execute insert
ExecuteDynamicInsert(instance)
End Sub
This will first check that the CreatedDate has a "blank" value, and will set the value if this is the case. This is only called for Inserts so will not affect edits or other operations. This supports both the manual setting of a value, and also duplicates the "default" behaviour that I previously had SQL Server perform.
Don’t forget the ExecuteDynamicInsert(instance) bit at the end – the Insert{Entity} method overrides the default LINQ to SQL DataContext insert so you have to explicitly insert the new record yourself.
Conficient Blog 