Mapping SQL datetime field to DateTimeOffset property

[Cory]

Hi,

 I'm in the process of creating OData services for a legacy SQL Server database which uses datetime fields. The issue is that OData doesn't support DateTime properties, and I can't figure out how to map a datetime field to a DateTimeOffset property using fluent mapping so that OData will work.

 I've tried:

 

configuration.HasProperty(x => x.CreatedOn).WithDataAccessKind(DataAccessKind.ReadWrite).ToColumn("CreatedOn").HasColumnType("datetime").IsNotNullable();

Unfortunately, the datetime to DateTimeOffset conversion throws an exception.

 Is it possible to map a datetime database field to a DateTimeOffset property? I noticed a WithConverter<> extension. Would creating a custom AdoTypeConverter class do the trick?

 

 Thanks,

-Cory

 

[Yavor Slavchev]

Hi Cory,

It is possible to map DateTimeOffset field to a DateTime sql type using a converter.
You can read this article how you can implement and wire up an AdoTypeConverter and I will provide you with a sample implementation how you can convert DateTimeOffset to DateTime and vise versa.
In order to convert the DateTime to a DateTimeOfffset value when you are reading from the database, you could use the following code in the Read() method:

public override object Read(ref DataHolder holder)
{
    bool isNull = holder.Reader.IsDBNull(holder.Position);

    holder.NoValue = isNull;

    if (isNull)
    {
        holder.ObjectValue = DateTimeOffset.MinValue;
    }
    else
    {
        DateTime value = (DateTime) holder.Reader.GetValue(holder.Position);
        DateTime.SpecifyKind(value, DateTimeKind.Utc);

        DateTimeOffset utcDateTimeOffset = value;
        holder.ObjectValue = utcDateTimeOffset;
    }
    return (holder.Box) ? holder.ObjectValue : null;
}

In order to convert the DateTimeOffset back to DateTime when you are saving in the database, you could use the following code in the Write() method of the converter:

public override void Write(ref DataHolder holder)
{
    holder.Parameter.DbType = System.Data.DbType.DateTime;
    if (!holder.NoValue)
    {
        DateTimeOffset dateTimeOffset = (DateTimeOffset)holder.ObjectValue;
        DateTime date = dateTimeOffset.Date;
        holder.Parameter.Value = date;
    }
}

I hope this will help you to resolve the issue. Let me know if you need further assistance.

Regards,
Yavor Slavchev
Telerik

 

[Cory]

Awesome​, I'll give it a try.

 Thanks!

 -Cory