How to read time offsets from oracle using Dapper

Question

I have data stored in an oracle TIMESTAMP WITH TIME ZONE column, and I am now trying to read it back into a C# DateTimeOffset variable using Dapper. The issue is that dapper ignores the offset value in the database, and always populates my variable with the environment's current offset.

Is there a simple way to get dapper to recognize the offset value from the database?

Basically I want to see something along these lines work:

var input=new DateTimeOffset(2016, 3, 15, 14, 30, 0, TimeSpan.Zero);
DateTimeOffset output;
using(var connection=new OracleConnection(QueryConnectionString)) {
    output=connection.ExecuteScalar<DateTimeOffset>("Select to_timestamp_tz('"+input.ToString("yyyy-MM-dd HH:mm zzz")+"', 'YYYY-MM-DD HH24:MI TZH:TZM') From DUAL");
}
Assert.AreEqual(input, output);

As written this gives an invalid cast exception, it appears that dapper reads it as a DateTime and then tries to cast it to DateTimeOffset, ignoring the offset value.

My code that queries a table and populates a class object defined with these types does not throw an error, but it populates the object instance with the local offset rather than the value in the database. So I would end up with 2016-03-15 14:30 -5 instead of 2016-03-15 14:30 +0, if I were working with the above input value.

Answer 1

So I noticed that some other people have been looking at this question and while I don't have a direct answer, I thought I would share the work around that we have been using...

Disclaimer: This is a bit of a kludge, and I would much prefer a cleaner approach, but it works.

Essentially what we ended up doing was converting the Timestamp With Timezone column to a formatted string in the select statement. In the C# class we then added a property that was a string representation of the DateTimeOffset, using the same format. We then used aliases in the query to ensure Dapper populated the value into the string property, the setter of which parses it back to a DateTimeOffset, and sets the original property.