DateTime precision in NHibernate and support for DateTime2 in NHibernate SchemeExport

Question

Hi,

I am then using Fluent NHibernate and its automapping feature to map the the following simplified POCO class:

public class Foo
{    
public virtual int Id { get; set; }    
public virtual datetime CreatedDateTime { get; set; }    
}

The CreatedDateTime field will map to a SQL DateTime by default. However if I do a test to check that the entity is being created correctly it fails. This is because the precision of the DateTime field is not maintained through to the SQL database. I undersatnd the reason behind this to be that a MS SQL Server DateTime can only hold milisecond precision by rounded to increments of .000, .003, or .007 (see http://msdn.microsoft.com/en-us/library/ms187819.aspx). For this reason NHibernate truncates the miliseconds when saving to the store. This results in my test failing when checking that the fields where persisted correctly as my .NET DateTime holds its miliseconds but the DateTime retrived after the save has lost its miliseconds and therefore the two are not truely equal.

To overcome this problem I have added the following mapping to the Foo object:

public class FooMap : IAutoMappingOverride<Foo>
{
    public void Override(AutoMapping<Foo> mapping)
    {
        mapping.Map(f => f.CreatedDateTime).CustomType("datetime2");     
    }
}

I understand that this mapping makes NHibernate persist the CreatedDateTime to a SQL type of datetime2, which can store the full precision that a .NET DateTime can. This works a treat and the test now passes.... yay :)

However with one pass come another fail... boo :( My test that checks the schema export now fails with the following error:

System.ArgumentException : Dialect does not support DbType.DateTime2
Parameter name: typecode

with a stack trace of:

at NHibernate.Dialect.TypeNames.Get(DbType typecode)
at NHibernate.Dialect.Dialect.GetTypeName(SqlType sqlType)
at NHibernate.Mapping.Column.GetDialectTypeName(Dialect dialect, IMapping mapping)
at NHibernate.Mapping.Table.SqlCreateString(Dialect dialect, IMapping p, String defaultCatalog, String defaultSchema)
at NHibernate.Cfg.Configuration.GenerateSchemaCreationScript(Dialect dialect)
at NHibernate.Tool.hbm2ddl.SchemaExport..ctor(Configuration cfg, IDictionary`2 configProperties)
at NHibernate.Tool.hbm2ddl.SchemaExport..ctor(Configuration cfg)

The code uses the NHibernate.Tool.hbm2ddl.SchemaExport object to call the Execute method.

I am using Fluent v1 and NHibernate v2.1.

I have also tried mapping my DateTime to a TimeStamp but couldn't even get the mapping working as the insert fails stating:

Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.

Does anyone know either how to get the SchemeExport working with a datetime2 OR how to get timestamp mapping working for a datetime property?

Thanks for any help

Answer 1

I ran into the same problem with a CreatedDate audit field on my business classes. I worked around it by setting the time using the value from a utility method. Hope this helps.

     /// <summary>
    /// Return a DateTime with millisecond resolution to be used as the timestamp. This is needed so that DateTime of an existing instance
    /// will equal one that has been persisted and returned from the database. Without this, the times differ due to different resolutions.
    /// </summary>
    /// <returns></returns>
    private DateTime GetTime()
    {
        var now = DateTime.Now;
        var ts = new DateTime(now.Year, now.Month, now.Day, now.Hour, now.Minute, now.Second, now.Millisecond, DateTimeKind.Local);
        return ts;
    }

Answer 2

In my domain it is acceptable to lose the milliseconds from datetimes in SQL Server. I therefore allow a tolerance in my persistance testers using this static helper (nunit implementation):

public static class AssertDateTime
{
    /// <summary>
    /// Checks that the DateTimes are no more than second apart
    /// </summary>
    /// <param name="Expected"></param>
    /// <param name="Actual"></param>
    public static void AreWithinOneSecondOfEachOther(DateTime Expected, DateTime Actual)
    {
        var timespanBetween = Actual.Subtract(Expected);

        if (timespanBetween > TimeSpan.FromSeconds(1))
            Assert.Fail(string.Format("The times were more than a second appart. They were out by {0}. Expected {1}, Actual {2}.", timespanBetween, Expected, Actual));
    }
}

Answer 3

Actually the NHibernate reference states that the DateTime nhibernate type will store the .NET DateTime as an SQL datetime truncated at the second level (no millisecond granularity)

As such it provides the Timestamp nhibernate type (type="Timestamp" at the mapping) which will store a .NET DateTime as an SQL datetime without truncation. Note here that an SQL-timestamp datatype is not needed.

Additionally note that if you are working with filters, the same rule applies at the filter definition: If you specify at DateTime parameter, the parameter's value will be truncated without milliseconds.

Check http://nhforge.org/doc/nh/en/index.html, chapter 5.2.2. Basic value types table Table 5.3 System.ValueType Mapping Types