MVC 3 date created and modified have default values and triggers in SQL Server 2008 R2, but still want value

Question

I have generated classes (DbContext) modelling my db (SQL Server 2008 R2), and in most of my tables I have the standard ModifiedDate and CreatedDate (No Nulls). Each of these has a default of getdate() in SQLServer, and I have a trigger to update ModifiedDate on any updates.

The generated views included the ModifiedDate and CreatedDate fields, which I don't want (the user shouldn't see these), so I've taken these out, but when adding a new entry using the generated Create view, I get the error "The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value".

I then added some default values, and it did add the record, but naturally it added my entered values, and not the SQL getdate() values, which I'd prefer (I want it to show the server time). Checking the object (db.SaveChanges()) the fields have a value of {1/01/0001 12:00:00 AM}.

How can I use these models without entering dates??? I've searched but haven't found my answer... ;-(

Answer 1

This is a common problem in both Entity Framework and Linq to SQL:

In Linq-To-SQL the model doesn't know about the default values, and so attempts to assign them as "null" - which in this case isn't acceptable. To fix this, open the DBML file and set the "Auto Generated Value" option to "true" for the fields in question.

In Entity Framework it's a little different: you set the "StoreGeneratedPattern" on the field to either "Identity" or "Computed". Here's the description:

http://msdn.microsoft.com/en-us/library/system.data.metadata.edm.storegeneratedpattern.aspx

EF will do this automatically for Identity type fields, but not for default value/not null fields. You may want to set your CreatedDate as "Identity" (updated only on insert) and your ModifiedDate as "Computed" (updated on insert and update).

The byproduct of this is that you then will not be able to set the fields via Linq at all - but that's fine in your use case, I think.