How to create custom database literal value converter

[gscpw]

I use Oracle. Oracle has a type TIMESTAMP WITH TIME ZONE, and they recommend using the TZR TZD format, which would look like this as a PL/SQL literal:

TIMESTAMP '1999-10-29 01:30:00 America/Los_Angeles PDT'

The appropriate .NET type for timestamp is DateTimeOffset. The problem is, DateTimeOffset has no way to specify TZR (such as America/Los_Angeles) and neither TZD (such as PDT in the example above).

Because DateTimeOffset does not contain sufficient information to construct the above literal, I think the only workaround would be to create a custom type, basically a class with a couple of properties, something like this:

public class OracleTimestampWithTimezone
{
    public DateTime DateTime { get; set; }
    public string TimeZoneRegion { get; set; }
    public string DaylightSavingTime { get; set; }
}

And now to the meat of the question. How do I go about creating a custom value converter which converts from OracleTimestampWithTimezone into timestamp literal (not a string!), and Oracle goes and interprets this as an actual literal value? My model basically needs to have a property of type OracleTimestampWithTimezone (or something like that), while the matching database column needs to be TIMESTAMP WITH TIME ZONE.

I understand that if EF Core offered the ability for user code to generate SQL literals and having these interpreted "as is" is quite dangerous, so perhaps this could somehow be worked around by using database functions? On column read, have the value be transformed using a stored function, which can output nvarchar2 and this could be subsequently converted using EF value converters to OracleTimestampWithTimezone. And on column write, a matching stored function could transform the EF value converter string back to Oracle timestamp. However I do not know how to do something like that either.

Please let me know if you have any tips, hints, or advice.

[roji]

@gscpw first, can you please confirm that Oracle actually stores the full timezone data (both TZR and TZD) in the column? In other words, when you insert a 1999-10-29 01:30:00 America/Los_Angeles PDT into a column of type TIMESTAMP WITH TIME ZONE, and then select it back, you get the exact same thing? I'm asking because sometimes databases accept something in the literal string representation that isn't actually stored (for example, PG TIMESTAMP WITH TIME ZONE accepts an offset in the literal representation, but converts to UTC and stores that, so the offset is lost).

Assuming the time zone is stored, then how does one insert such a value using the Oracle ADO.NET driver (without EF)? There needs to be a way to create an OracleParameter (DbParameter) whose value contains all the necessary information; this page seems to indicate that the Oracle ADO.NET driver already provides an OracleTimeStampTZ (similar to your proposed OracleTimestampWithTimezone).

If that's the case, have you tried using OracleTimeStampTZ with EF Core (e.g. as a property on your entities)? If that doesn't work, then the Oracle EF provider would need to add support this. It's possible to write a type mapping plugin which adds support, but this really is something that belongs in the provider itself.

[gscpw]

@roji I have stored 1999-10-29 01:30:00 America/Los_Angeles PDT in a column TIMESTAMP WITH TIME ZONE, and querying it back yielded 29-OCT-99 01.30.00.000000 AM AMERICA/LOS_ANGELES. TZR is preserved, while TZD is supposedly used only to check for ambiguity while storing the data.

Using ADO.NET Oracle Driver, I was able to insert the OracleTimeStampTZ like this:

var connection = db.Database.GetDbConnection();
connection.Open();

using var param = new OracleParameter();
param.OracleDbType = OracleDbType.TimeStampTZ;
param.Value = new OracleTimeStampTZ(DateTime.Parse("1999-10-29 01:30:00"), "America/Los_Angeles");

using var cmd = connection.CreateCommand();
cmd.CommandText = "INSERT INTO tz_test (tz_value) VALUES (:1)";
cmd.Parameters.Add(param);

cmd.ExecuteNonQuery();

And that works fine. However, using OracleTimeStampTZ with EF Core (as a property on my entity) does not work - the exception is:

'The property 'TzTest.TzValue' could not be mapped because it is of type 'OracleTimeStampTZ', which is not a supported primitive type or a valid entity type. Either explicitly map this property, or ignore it using the '[NotMapped]' attribute or by using 'EntityTypeBuilder.Ignore' in 'OnModelCreating'.'

You mentioned writing a type mapping plugin. How difficult is this? Do I need to have intimate knowledge of the Oracle EF provider to write this plugin, or is it rather straightforward? I have never done anything like that before and don't really know where to begin. Nevertheless, I will definitely ask Oracle to add this support, but who knows if/when this will land on NuGet.

[roji]

@gscpw writing a type mapping plugin isn't very complicated - I'd advise looking at one of the existing ones and copying it. For example, the Npgsql NodaTime plugin adds the proper type mappings for mapping NodaTime date/time types to the PostgreSQL timestamp types.

However, support for this really does belong in the provider itself.

I'm going to go ahead and close this, since this is an Oracle-specific issue; but feel free to continue posting back if you need more guidance etc.