How should I annotate CreatedOn and ModifiedOn columns with EF 4.1?

Question

In our database, every table has two DateTime columns, CreatedOn and ModifiedOn, set via triggers in SQL Server. CreatedOn is set on INSERT, and ModifiedOn is set on INSERT and UPDATE.

I am trying to use Entity Framework 4.1. How should I annotate/configure the two properties?

I think it involves the annotation [DatabaseGenerated(DatabaseGeneratedOption.Computed)], but should I use that annotation for both, or should I set [DatabaseGenerated(DatabaseGeneratedOption.Identity)] on the CreatedOn field?

According to MSDN Identity simply implies that The database generates a value when a row is inserted., which seems true here.

Also, should I use [Timestamp]?

Answer 1

Use Identity for CreatedOn and Computed for ModifiedOn. Identity means that value is set only during insert and returned back to application. Computed is set during each modification (including insert) and value is returned back to the application after each executed insert or update.

Just be aware that neither of these properties can be set in the application. Computed columns also can't be part of primary key or foreign key (it will not be your case).

This will only work with existing database. When using code-first Computed can be set only for timestamp or rowversion.

Timestamp is used for optimistic concurrency. If you mark a column as timestamp each update will contain condition WHERE timestampColum = @lastKnownValue. It will update the record only if last known value is the same as current value. If the value is different you will get an exception. It is usually used with timestamp SQL type. Using it with datatime would require some tests. Value of SQL datatime is not the same as value in .NET.