Mapping SQL datetime field to DateTimeOffset property

3 posts, 0 answers
  1. Cory
    Cory avatar
    10 posts
    Member since:
    Nov 2014

    Posted 16 Oct 2015 Link to this post

    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

     

  2. Yavor Slavchev
    Admin
    Yavor Slavchev avatar
    22 posts

    Posted 21 Oct 2015 Link to this post

    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
     
    Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
  3. DevCraft banner
  4. Cory
    Cory avatar
    10 posts
    Member since:
    Nov 2014

    Posted 21 Oct 2015 Link to this post

    Awesome​, I'll give it a try.

     Thanks!

     -Cory

Back to Top