Persisting DateTimeOffset with XPO

23 January 2017

After my recent update about spatial data in conjunction with XPO, I heard that we also have requests about the DateTimeOffset type that's been available in the .NET Framework for a long time. I decided to create a quick example along the same lines as the spatial data example. Like before, I'm using SQL Server, which has a "datetimeoffset" field type. To persist to other database engines, you would have to find a compatible type and convert values accordingly - feel free to get back to me if you have any specific questions about this!

My full example is available in github here: https://github.com/oliversturm/xpo-sqlserver-datetimeoffset

For some of the details and additional background, I recommend you read my posts about spatial data (here and here), since many of the same considerations apply. For DateTimeOffset, I have introduced this ValueConverter:

public class DateTimeOffsetConverter : ValueConverter {
  public override object ConvertFromStorageType(object value) {
    return value;
  }

  public override object ConvertToStorageType(object value) {
    if (value is DateTimeOffset) {
      var dto = (DateTimeOffset)value;
        return dto.ToString();
      }
      else
        return value;
  }

  public override Type StorageType {
    get { return typeof(string); }
  }
}


In my persistent type, I have implemented the DateTimeOffset property like this:

DateTimeOffset dto;
[ValueConverter(typeof(DateTimeOffsetConverter))]
[DbType("datetimeoffset")]
public DateTimeOffset Dto {
  get { return dto; }
  set { SetPropertyValue("Dto", ref dto, value); }
}


Finally, I'm using a custom provider with the following implementation of ReformatReadValue. Some detailed thoughts about this approach are described in my previous posts. In a nutshell, this code deactivates the default behavior of the standard providers, which assume that read values of non-standard types will be convertible automatically. When dealing with types that are recognized by the underlying .NET database drivers, this behavior results in issues because default conversions are not available. Since an additional conversion is a superfluous extra step anyway, deactivating the mechanism in the provider is a reasonably choice.

protected override object ReformatReadValue(object value, ReformatReadValueArgs args) {
  if (value != null) {
    Type valueType = value.GetType();
    if (valueType == typeof(DateTimeOffset))
      return value;
  }
  return base.ReformatReadValue(value, args);
}


We are considering options to improve the XPO type mapping extensibility, so it is possible that there will be easier approaches available in the future. However, it's always been one of the greatest strengths of XPO that it enables truly database independent coding, and it is a priority for us not to disturb this basic concept. Unfortunately, data types that are not supported across the board of RDBMSs out there are therefore always special cases from the XPO point of view. Please feel free to let us know your thoughts on this!





Tags
7 comment(s)
Tomas Kouba

I see possible problem ToString() without explicit CultureInfo  and format in machine to machine conversion. In cs-CZ could be '01.02.2017 15:01:14 +01:00' but invariant culture is '02/01/2017 15:01:14 +01:00'.

1 February, 2017
Oliver Sturm (DevExpress)

Hi Tomas,

Yes, you are correct. This is a well-known and frequently-discussed aspect of the DateTimeOffset type (search "datetimeoffset timezone" to find a number of StackOverflow threads). Microsoft tries to help you decide between the types they have in the .NET framework: msdn.microsoft.com/.../bb384267(v=vs.110).aspx

If you need to work with complex date/time information, I recommend you check out Noda Time (http://nodatime.org/), which provides a ZonedDateTime type (among others). One issue you will encounter though is that of persisting complex date/time information, because there are no specialized data types available in the RDBMS systems (or if there are, they are not standardized). So whether or not you are using clever types like ZonedDateTime on the client side, you will have to persist the details to several fields instead of just one, which cannot currently be done on the data store level and requires some work by the application developer.

Thanks

Oliver

3 February, 2017
Peter Metz

@Tomas, About problems with different CultureInfo ... for me it works with formatting:

public override object ConvertToStorageType(object value)

       {

           if (value is DateTimeOffset)

           {

               var dto = (DateTimeOffset)value;

               //example 2017-09-21 14:49:34.0092707 +02:00

               return dto.ToString("yyyy-MM-dd HH:mm:ss.fffffff zzz");

           }

           else

               return value;

       }

3 November, 2017
Peter Metz

Hello Oliver, I'm using your solution and it's fine when accessing the MS SQL DB directly. But when I do wrap it through WCF with DataStoreService and DataStoreClient, every native supported type is working well but not DateTimeOffset type.

Any suggestions?

Thanks,

Peter

3 November, 2017
Oliver Sturm (DevExpress)

Hi Peter,

Our support team is looking into this issue for you and they'll follow up to your Support Center query as soon as they have feedback. Your patience is greatly appreciated!

Oliver

6 November, 2017
Peter Metz

Hi Oliver, thanks - yes, there is a solution, it's reformatting the DateTimeOffset before serializing and sending to client.

T571824 contains an example project.

The reformatting is fine until you can use value converter on client side by attributing properties with  [DateTimeOffsetConverter] (...).

But when you have uow.ExecuteQuery("select * from table")  - this fails.

Is there any chance to solve that?

Thanks,

Peter

8 November, 2017
Oliver Sturm (DevExpress)

Hi Peter,

I haven't looked into that myself, especially since I never ExecuteQuery... but of course it's a valid question. I see that our devs recommended coding a service instead, which is exactly what I have done in the past when an XPO based client application required non-XPO operations to run on the server.

Of course this approach introduces complexity to the deployment and overall architecture of an application, for edge case scenarios where a db-independent query seems a reasonable compromise. I would suggest there could be a step in between - perhaps you could query from a database view that converts the DateTimeOffset into a set of fields (or a string) that can be retrieved without conversion issues?

Oliver

9 November, 2017

Please login or register to post comments.