CODEWITHSUNDEEP
linqentity-frameworkdatetimeoffset
sgilmore
sgilmore

Reputation: 21

Linq DateTimeOffset throws InvalidTimeZoneException

My Linq query of DateTimeOffset column always throws InvalidTimeZoneException. The data appears to be correct. Any idea what's happening?

Details: 

    Oracle Column:  CREATED_DATETIME  TIMESTAMP(0) WITH TIME ZONE

    EF MAPPING: public Nullable<System.DateTimeOffset> CREATED_DATETIME { get; set; }

    DataAccess: ODP.net Oracle.DataAccess

    Data Sample: (Timezone column available but not used)

        CREATED_DATETIME              TIMEZONE_NAME
        8/16/2013 5:06:05 PM +00:00   US/Central
        8/16/2013 5:35:06 PM +00:00   US/Mountain

Code: 

var q = from isr in pc.ISRs
                select isr.CREATED_DATETIME;
        try
        {
            DateTimeOffset? value = q.First();
        }
        catch (InvalidTimeZoneException tze)
        {
            throw new ApplicationException(tze.Message);
        }
        catch (Exception e)
        {
            throw new ApplicationException(e.Message);
        }

        var orders = from o in q select o;

Upvotes: 2

Views: 1143

Answers (1)

Matt Johnson-Pint
Matt Johnson-Pint

Reputation: 241563

Oracle's TIMESTAMP WITH TIME ZONE type, (aka TIMESTAMPTZ) is a funny creature. It stores a date, a time, and either a fixed "time zone offset" or a "time zone region name".

When there's a fixed offset, then the DateTimeOffset type in .Net would be a good match, and it is not surprising that Entity Framework is using that as the default. (Although technically, the mapping probably comes from the underlying ODP.net provider rather than from EF.)

But when it's a time zone region name, there are going to be issues:

  • Those time zone names are IANA time zones, and aren't usable directly with the TimeZoneInfo class. They would need to be translated. See the section on time zone databases in the timezone tag wiki.

  • .NET doesn't have any type that represents a DateTime or DateTimeOffset paired with a time zone.

  • The Oracle data doesn't actually include the time zone name. Instead, it includes a pointer back to an id in the system data within the Oracle database. So any resolution of this name from the raw bits would have to be done while a connection to the database was still open. Not to long ago, I answered a question about this and how it relates to Java. You can read my response here, which mostly applies still for .NET. (Although I'm sure the implementation is quite different in ODP.net.)

It's worth pointing out that the Noda Time library includes both IANA time zone data, and the ZonedDateTime type, which could fully represent an Oracle TIMESTAMPTZ. There is no direct mapping, but I suppose it should be possible to construct a ZonedDateTime when calling your Oracle database.

However, there is currently no support for Noda Time with Entity Framework, so that would get in the way here. (That is on my agenda, but I have not looked into it very deeply yet.)

So most likely, the only way you will be able to use the TIMESTAMPTZ type within Entity Framework is to make sure you are using fixed offset zones only. Using it with an Oracle "time zone region name" is not going to work. At least - not yet.

It's a shame really. The region names are a nice feature, because then you are automatically getting the benefit of daylight saving time changes being incorporated into the calculation.

Upvotes: 1

Related Questions