Let's assume I have a table with a CreatedBy Datetime column with a default value of GETDATE()...
Enter LinqToSql
From what I understand so far, I have two options:
What if I want to keep the option to set the value, but not be forced to supply it (like I can in T-SQL)?
Have the column be nullable. Create a trigger that fires instead of insert and if the value supplied is null, set it to the current date. Provide a partial method for the property changing method for that field that throws an exception if the field is attempted to be set to null.
This will not force you to supply a value, but if you do try to set it you won't be able to null it out. The trigger will supply the default on insert and on any update it should be guaranteed to have a value since the property changing method won't allow it to be set to null.
EDIT: If you wanted to avoid the trigger and didn't mind the potential of a (likely small) difference between the actual time of insertion and the column value, you could implement the partial method for created and set the backing field for the date directly. By not using the property I think you would avoid spurious updates as the property changed event wouldn't be triggered. You'd have to test this to see if it works the way you want. One advantage to this is that it could be fully unit tested without involving the database.
FWIW, I usually just go the autogenerated route. I don't have any scenarios where I would be manually setting the CreatedDate on a record. I might go in and update one by hand in an emergency, but I can't even remember at time when I had to do that.
You're correct, those are your only two options for a given DataContext.