DateTime precision in NHibernate and support for DateTime2 in NHibernate SchemeExport

Question

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.

However with one pass comes another fail: 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?

Answer 1

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" in the mapping) which will store a .NET DateTime as an SQL datetime without truncation. Note here that an SQL timestamp datatype is not needed and will infact break if you have more than one timestamp column in one table. It's thus important to differentiate between the sql-type and type attributes in the NHibernate mapping.

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

Check out chapter 5.2.2. Basic value types, Table 5.3 System.ValueType Mapping Types.

Answer 2

For anyone looking to actually keep the nanosecond part of the date, you'll have to use DateTime2 as the sql-column type as well as the Nhibernate DateTime2 type.

Here's my convention for setting this up (using fluent)

public class DateTimeConvention : IPropertyConvention, IPropertyConventionAcceptance
{

    public void Accept(IAcceptanceCriteria<IPropertyInspector> criteria)
    {
        criteria.Expect(x => x.Type == typeof(DateTime) || x.Type == typeof(DateTime?));
    }
    public void Apply(IPropertyInstance instance)
    {
        instance.CustomSqlType("DateTime2"); //specify that the sql column is DateTime2
        instance.CustomType("DateTime2"); //set the nhib type as well
    }
}

And to activate the convention:

 var v = Fluently.Configure()
         .Database(MsSqlConfiguration.MsSql2008
         .ConnectionString(d => d.FromConnectionStringWithKey("connstring"))
         .ShowSql())
         .Mappings(m => m.FluentMappings.AddFromAssemblyOf<IRepository>()
         .Conventions.AddFromAssemblyOf<IRepository>()) //this adds your convention
         .BuildSessionFactory();

Using this you'll get to keep nanoseconds when storing your datetimes.

Answer 3

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 4

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 5

I was able to get my optimistic locking worked out using the below: (using datetime2).

Note, I used the name (and case of the datatype-name) from here: http://msdn.microsoft.com/en-us/library/system.data.dbtype.aspx "DateTime2" is in my mapping code (under CustomType) and not the Sql Server data-type-case ("datetime2"). I'm not sure if that makes a difference but I wanted to point it out.

Fluent Mapping:

public class DogBreedMap : ClassMap<DogBreed>
{
    public DogBreedMap()
    {
        Id(x => x.DogBreedUUID).GeneratedBy.GuidComb();
        OptimisticLock.Version();
        Version(x => x.Version)
           .Column("MyTimestamp").CustomType("DateTime2");
    }
}




public partial class DogBreed
{

    public DogBreed()
    {
        CommonConstructor();
    }

    private void CommonConstructor()
    {
        this.Version = DateTime.MinValue; /*I don't think this is necessary*/
    }

    public virtual Guid? DogBreedUUID { get; set; }

    public virtual DateTime Version { get; set; }
}

The Sql Server column is created at:

[MyTimestamp] [datetime2](7) NOT NULL

And my basic tests work and I (correctly) receive an exception like this (when someone else has updted the row)

Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect): [DogBreed#abcabc1d-abc4-abc9-abcb-abca01140a27]

at NHibernate.Persister.Entity.AbstractEntityPersister.Check(Int32 rows, Object id, Int32 tableNumber, IExpectation expectation, IDbCommand statement)

at NHibernate.Persister.Entity.AbstractEntityPersister.Update(Object id, Object[] fields, Object[] oldFields, Object rowId, Boolean[] includeProperty, Int32 j, Object oldVersion, Object obj, SqlCommandInfo sql, ISessionImplementor session) at NHibernate.Persister.Entity.AbstractEntityPersister.UpdateOrInsert(Object id, Object[] fields, Object[] oldFields, Object rowId, Boolean[] includeProperty, Int32 j, Object oldVersion, Object obj, SqlCommandInfo sql, ISessionImplementor session) at NHibernate.Persister.Entity.AbstractEntityPersister.Update(Object id, Object[] fields, Int32[] dirtyFields, Boolean hasDirtyCollection, Object[] oldFields, Object oldVersion, Object obj, Object rowId, ISessionImplementor session) at NHibernate.Action.EntityUpdateAction.Execute() at NHibernate.Engine.ActionQueue.Execute(IExecutable executable) at NHibernate.Engine.ActionQueue.ExecuteActions(IList list) at NHibernate.Engine.ActionQueue.ExecuteActions() at NHibernate.Event.Default.AbstractFlushingEventListener.PerformExecutions(IEventSource session) at NHibernate.Event.Default.DefaultFlushEventListener.OnFlush(FlushEvent event) at NHibernate.Impl.SessionImpl.Flush() at NHibernate.Transaction.AdoTransaction.Commit()