FluentNHibernate and SQL Date Generation
So you’d like your SQL entry to have a system generated date/time, eh?
Here is a sample table:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE [dbo].[AuditLog] ( Id int IDENTITY(1,1) PRIMARY KEY, EventUtc datetime2(7) DEFAULT(SYSUTCDATETIME()) NOT NULL, EventOffsetUtc datetimeoffset(7) DEFAULT(SYSDATETIMEOFFSET()) NOT NULL, EntityContextUid uniqueidentifier, EntityContextName nvarchar(256), EntityContextType varchar(128), UserLogin nvarchar(128), EventName varchar(128), AppContext varchar(64), EventData nvarchar(max), ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] |
To spare you hours dealing with this error:
|
1 2 3 4 |
System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM. |
What you need to do is to use the following mapping for your date/time columns:
|
1 2 3 4 5 6 7 8 9 10 |
Map(a => a.EventUtc).Column("EventUtc") .CustomSqlType("datetime2(7)") .Not.Nullable() .Default("SYSUTCDATETIME()") .Generated.Insert(); Map(a => a.EventOffsetUtc).Column("EventOffsetUtc") .CustomSqlType("datetimeoffset(7)") .Not.Nullable() .Default("SYSDATETIMEOFFSET()") .Generated.Insert(); |
Special thanks to this Stackoverflow thread.
