Persisting DateTimeOffset with XPO

UPDATED for Microsoft SQL Server and Oracle with DevExpress v26.1+

New function groups include:

• Current date/time functions (LocalDateTimeToday, LocalDateTimeNow, LocalDateTimeThisMonth, etc.)
• Outlook-style interval functions (IsOutlookIntervalToday, IsOutlookIntervalTomorrow, etc.)
• Time-frame functions (IsThisYear, IsYearToDate, etc.)
• Comparison functions (IsSameDay, IsSameHour, etc.)
• Date difference functions (DateDiffDay, DateDiffMonth, etc.)
• Date component functions (GetDate, GetMonth, GetOffset, etc.)
• Intraday functions (BeforeMidday, AfterMidday, etc.)
• Date arithmetic functions (AddDays, AddMonths, etc.)
• Range functions (InRange, InDateRange, etc.)

These functions can evaluate values in a client-specified time zone — you do not need to manually convert DateTimeOffset values to local DateTime values. DateTimeOffset functions are also compatible with EF Core Server Mode (Microsoft SQL Server 2016+ and Oracle). EntityServerModeSource generates Microsoft SQL Server–compatible LINQ queries for DateTimeOffset properties and processes results using native DateTimeOffset support.

v26.2+ will add support to DevExpress data-aware controls. We also plan to support the DateTimeOffset data model properties in key XAF List and Property Editors (by the end of 2026). This enchantment will be especially useful for applications with users in multiple time zones.

For more information, see also XPO docs at Microsoft SQL Server Data Types Mapping and Oracle Data Types Mapping. 

NOTE: The "manual" solution illustrated below is still applicable to RDBMS other than Microsoft SQL Server and Oracle
or DevExpress v25.2 and earlier versions.

---------------------------

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!

Free DevExpress Products - Get Your Copy Today