Saving Nodatime Instant in SQL Server with ServiceStack / OrmLite

Question

I use NodaTime Instantto store date and time in my DTOs with ServiceStack. I specified the SQL type in DTO as datetimeoffset, and ServiceStack correctly creates a table with this type. However, when saving, I get InvalidCastException.

A 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;
}

Specific InvalidCastException error with details Failed to convert the parameter value from string to string.

I do not know why it is converted to a string when the database type datetimeoffset. I need to tell ServiceStack how to convert a value to something that works with SQL type?

Update

@mythz . DATETIME2 SQL ( , ):

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);
    }
}

AppHost.cs:

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

FromDbType. , .

- ServiceStack , , DateTimeKind.Local

Answer 1

[CustomField] OrmLite, , , OrmLite .

, Custom Type Converter, v4.0.44.