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<MyObject>
{
    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!

Have you read this [post](/a/35700837/1178314) about defaults? Defining defaults in mapping is almost only for dml generation (generating a default constraint in db), not for defining default values on your newly instantiated entities. This should be done through constructor or fields initializers, as with any class. — Frédéric, Mar 22 '16 at 13:26
+1 @Frédéric. This clarifies the constructor question. It requires to write a private Init() method and call it from the constructor, since one should not assign values to virtual props in the constructor.... — ThommyB, Mar 22 '16 at 13:51

score 3 · Accepted Answer · answered Mar 28 '16 at 16:58

Usually for this type of mapping I do the following in my mapping:

DynamicInsert();
DynamicUpdate();

This way if you have nullable types in C# and you don't set them to anything nhibernate will not include them in the insert or update statement. I never really like it when nhibernate is updating columns that weren't changed in the code anyway.

Furthermore when you specify .Not.Nullable(); and .Default("getdate()") in the mapping all this is used for is schema generation. It's not actually used by nhibernate to do any validation or defaulting. That is left up to the database.

thanks for that. but sometimes i need to update the values to empty. and I havent been able to get the .Default( defaultValue ) working in any way - the only thing is set it in the MsSql database. or just include the default value into the entity — Andrew Rebane, Feb 13 '17 at 01:18

score 1 · Answer 2 · edited May 23 '17 at 12:08

You have defined this column as NOT NULL in the database and Nullable() in the mapping. FluentNHibernate is sending DbNull to the database, which is getting rejected, since the database column is not nullable.

You should decide where your logic resides. Is this your code or the database who is the master?

Setting default value in FlientNHibernate was discussed, for example, in this question.

Suggested code from there is:

Map(x => x.SubmitionDate).Default("getdate()").Not.Nullable();

In general, I would always advise to use NHibernate Profiler (paid tool) to see what queries go to the database and why they fail. Invaluable for optimisation, you are using ORM, be careful now!

Thanks, the `Nullable()` is a typo, I had to strip down my code a bit, in reality it is a bit more complex. — ThommyB, Mar 22 '16 at 08:16

Fluent NHibernate mapping for DateTime with default value

2 Answers2