Persisting Nodatime Instant in SQL Server with ServiceStack / OrmLite

Question

I'm using NodaTime Instant for date/time storage in my DTOs with ServiceStack. I have specified the SQL type in the DTO as datetimeoffset, and ServiceStack correctly creates the table with that type. However, upon saving, I get an InvalidCastException.

Simple example:

public class ItemWithInstant
{
    public int Id { get; set; }
    public string Name { get; set; }
    [CustomField("DateTimeOffset")
    public Instant DateCreated { get; set; }
}

In the service:

public object Post(CreateItemWithInstant request)
{
    var dto = request.ConvertTo<ItemWithInstant>();
    Db.Save(dto); // ERROR here
    return dto;
}

The specific error is an InvalidCastException with the detail of Failed to convert parameter value from a Instant to a String.

No idea why it's converting to a string when the database type is DateTimeOffset. Do I need to tell ServiceStack how to convert the value to something that works with the SQL type?

Update

Thanks to @mythz answer, I created a custom converter. I also ended up going to DATETIME2 for the SQL data type (don't think that makes much of a difference):

public class SqlServerInstantToDatetimeConverter : OrmLiteConverter
{
    public override string ColumnDefinition { get { return "DATETIME2"; } }

    public override DbType DbType { get { return DbType.DateTimeOffset; } }

    public override object ToDbValue(Type fieldType, object value)
    {
        var instantValue = (Instant) value;
        return instantValue.ToDateTimeUtc();
    }

    public override object FromDbValue(Type fieldType, object value)
    {
        var datetimeValue = DateTime.SpecifyKind((DateTime)value, DateTimeKind.Utc);
        return Instant.FromDateTimeUtc(datetimeValue);
    }
}

I then registered it in my AppHost.cs file:

Register<IDbConnectionFactory>(new OrmLiteConnectionFactory(Settings.Default.LocalSqlConnectionString, SqlServerDialect.Provider));
SqlServerDialect.Provider.RegisterConverter<Instant>(new SqlServerInstantConverter());

Don't forget the FromDbType override. I forgot it initially and the field wasn't being output.

One other caveat -- since ServiceStack wants to localize all dates, I had to use the info in this answer to force all dates to a DateTimeKind.Local

Answer 1

[CustomField] just tells OrmLite what column definition to use when creating the table, i.e. it doesn't provide any indication on how OrmLite should treat unknown types.

You can support new fields types by registering a Custom Type Converter that's now available in the latest v4.0.44 release.