Fluent Nhiberhate And Missing Milliseconds

Question

I am using Fluent Nhibernate and Nhibernate for my current project. I need to record the time to the millisecond. I have this for my mapping

            Map(x => x.SystemDateTime)
            .CustomType("Timestamp")
            .Not.Nullable();

I genertaed the hbm.xml files and the line is the following:

<property name="SystemDateTime" type="Timestamp">
  <column name="SystemDateTime" not-null="true" />
</property>

I have read this is the fix, but the records in the database do not have the milliseconds. Has anyone solved this issue. And I have tried CustomSqlType also.

Thanks

Tradeswoman

Which database are you using, and what is the type (in database) of the Timestamp column? – Krasnodar

also, the data you already had have been truncated and that information has been lost. – Superimpose

I'd also like to know which DB is being used. SQLite, for example, does not store milliseconds, and requires workarounds. – Belligerence

Answer 1

We use the same approach as you and it does correctly store the milliseconds. If you weren't always doing it that way though your old records will have lost their milliseconds.

Assuming you want to store milliseconds for all of your DateTime fields, you could use a convention:

public class OurPropertyConventions : IPropertyConvention
{
    public void Apply(IPropertyInstance instance)
    {
        Type type = instance.Property.PropertyType;
        if (type == typeof(DateTime) || type == typeof(DateTime?))
            instance.CustomType("Timestamp");
    }
}

Your mappings can now just be:

Map(x => x.SystemDateTime).Not.Nullable();

Soave

Any idea if this can be done in configuration, ie. Fluently.Configure().Conventions.Add(...)? – Revengeful

var factory = Fluently.Configure() .Mappings(m => m.FluentMappings.Conventions.Add(new TimestampConvention())) .BuildSessionFactory(); – Laboratory

@AshBurlaczenko another example for how to add conventions to the configuration would be var sessionFactory = Fluently.Configure(rawConfig).Database(PersistenceConfigurer).Mappings(m => m.FluentMappings.AddFromAssembly(typeof(OurPropertyConventions).Assembly).Conventions.AddFromAssemblyOf<OurPropertyConventions>()); – Tweak

Answer 2

Revisiting this, as previous answers were slightly incomplete.

Assuming you want all your DateTime fields stored with full details and millisecond precision, use TIMESTAMP(6) WITH TIME ZONE as the SQL column type. You'll need a property convention for FluentNHibernate:

using System;
using FluentNHibernate.Conventions;
using FluentNHibernate.Conventions.AcceptanceCriteria;
using FluentNHibernate.Conventions.Inspections;
using FluentNHibernate.Conventions.Instances;

internal class DateTimeMapsToTimestampConvention
    : IPropertyConvention, IPropertyConventionAcceptance
{
    public void Apply(IPropertyInstance instance)
    {
        instance.CustomType<NHibernate.Type.TimestampType>();
    }

    public void Accept(IAcceptanceCriteria<IPropertyInspector> criteria)
    {
        criteria.Expect(x => x.Type == typeof(DateTime)
            || x.Type == typeof(DateTime?));
    }
}

You then add this convention to your fluent configuration session factory building code:

var factory =  Fluently.Configure().Mappings(
  m => assemblies.ForEach(
    assembly => m.FluentMappings.AddFromAssembly(assembly)
                .Conventions.Add(new DateTimeMapsToTimestampConvention())))
            .BuildSessionFactory();

Spritsail

Answer 3

This did not work for me:

Map(x => x.DateCreated).Column("DATE_CREATED").CustomSqlType("TIMESTAMP(6) WITH TIME ZONE").Not.Nullable();

while this did:

Map(x => x.DateCreated).Column("DATE_CREATED").CustomType("timestamp").CustomSqlType("TIMESTAMP(6) WITH TIME ZONE").Not.Nullable();

I have no idea why, though :/

Fredela

For me CustomType("timestamp") worked but CustomType("Timestamp") didn't – Commodious