Fluent NHibernate mapping for DateTime with default value

Question

I know that there are many threads about this and I have read most of them. However for me a couple of things remain unclear and still do not work.

If I have on my database schema a field of type DateTime and I like to assign it a default value I would do something like this:

create table [mySchema].[MyTable](
    ObjectGuid uniqueidentifier CONSTRAINT Id PRIMARY KEY,
    SomeTextToStore nvarchar(128) NULL,
    CDate datetime NOT NULL DEFAULT GETDATE(),
    CUser nvarchar(64) DEFAULT CURRENT_USER
);
GO

(Don't know if it is important: I am using SQL Server Express 2014. Fluent configuration is for SQL Server 2012.)

This works fine when doing an INSERT from ISQL, inserts a timestamp of the moment when the record was added.

Using fluent I would write something like this:

Domain:

public class MyObject
{
    public virtual Guid Id {get; set}
    public virtual string SomeTextToStore {get; set;}
    public virtual DateTime? CDate {get; set;}
    public virtual string CUser {get; set;}
}

NOTE: I made CDate nullable!

And a mapping class like this:

class MyObjectMap : ClassMap
{
    public MyObjectMap()
    {
        Table("MySchema.MyTable");
        Id(x => x.Id).GeneratedBy.GuidComb();
        Map(x => x.SomeTextToStore).Length(128).Nullable();
        Map(x => x.CDate).Not.Nullable().Default("getdate()");
        Map(x => x.CUser).Not.Nullable().Default("CURRENT_USER);
    }
}

In the program (in my case this is a library that can be called from several type of programs) I do something like:

public void EnterSomeText()
{
    using (var session = sessionManager.OpenSession())
    {
        using (var transaction = session.BeginTransaction())
        {
            var myObj = new MyObject();
            myObj.SomeTextToStore("bla bla bla");
            session.SaveOrUpdate(myObj);
            transaction.Commit();
        }
        session.Close();
    }
}

This ends always in a DateTime overflow exception! (SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM)

It looks like not passing a value to the file CDate is causing the problem. When I add the default in my library like such it works:

...
myObj.SomeTextToStore("bla bla bla");
myObj.CDate = DateTime.Now;   // <---- Set the date here
session.SaveOrUpdate(myObj);
...

But this is not really the solution....

Questions:

What am I doing wrong / missing ?
What is the correct strategy when defining defaults? Doing it on the database or just in code? (When starting a plain new vanilla project, I would prefer doing everything from C# code, even create and later update the schema...)
Regarding domain classes: Is it wise to create constructors, that fill fields with defaults?

I do this for the field CUser because in CUser I like to add the current user context

public MyObject()
{
   var o = System.Security.Principal.WindowsIdentity.GetCurrent();
   if (o != null)
   {
       CUser = o.Name;
   }  
}

Instead of filling the CDate field with the current date in my DB-access layer library I could do it also in the constructor of the domain-class like such:

public MyObject()
{
   var o = System.Security.Principal.WindowsIdentity.GetCurrent();
   if (o != null)
   {
       CUser = o.Name;
   } 
   CDate = DateTime.Now; 
}

Many thanks for your help and comments!

Fluent NHibernate mapping for DateTime with default value

2 Answers