How can I get Entity Framework with Oracle to send fractional seconds to a database in a query?

Question

I seem to be having the opposite problem from the asker of this question . I cannot seem to get EF to bind any DateTime objects as an Oracle timestamp when used as part of a LINQ query. They are being bound as date objects, which does not preserve the milliseconds on the DateTime object.

In order to get EF to bind DateTime objects as timestamp variables in insert statements, I needed to heed the advice presented in the answer to this question. The fix involves modifying OnModelCreating to specify the precision of the column. I also added the column type as part of my troubleshooting.

modelBuilder.Entity<EntityWithTimestamps>().Property(p => p.NullableTimestampStart)
    .HasColumnType("timestamp")
    .HasPrecision(6);
modelBuilder.Entity<EntityWithTimestamps>().Property(p => p.NullableTimestampEnd)
    .HasColumnType("timestamp")
    .HasPrecision(6);

Here is my query:

DateTime queryTime = new DateTime(2018, 07, 11, 11, 23, 00, 501);
EntityWithTimestamps ent = Db.TableOfEntitiesWithTimestamps
    .Where(ex => (ex.NullableTimestampStart <= queryTime
        && (ex.NullableTimestampEnd > queryTime || ex.NullableTimestampEnd == null)))
    .FirstOrDefault();

Here is how EF formats the query - it's binding the queryTime DateTime variable as a date. A date object will not have sufficient resolution to include fractional seconds. In my opinion, it should be sending my queryTime variable over as a timestamp.

SELECT 
    "Extent1"."STRINGPAYLOAD" AS "STRINGPAYLOAD", 
    "Extent1"."TIMESTAMPSTART" AS "TIMESTAMPSTART", 
    "Extent1"."TIMESTAMPEND" AS "TIMESTAMPEND", 
    "Extent1"."NULLABLETIMESTAMPSTART" AS "NULLABLETIMESTAMPSTART", 
    "Extent1"."NULLABLETIMESTAMPEND" AS "NULLABLETIMESTAMPEND", 
    "Extent1"."TIMESTAMPWITHOUTPRECISION" AS "TIMESTAMPWITHOUTPRECISION"
FROM "SCHEMA_NAME"."TABLE_WITH_TIMESTAMPS" "Extent1"
WHERE (
    ("Extent1"."NULLABLETIMESTAMPSTART" <= :p__linq__0)
    AND (("Extent1"."NULLABLETIMESTAMPEND" > :p__linq__1)
    OR ("Extent1"."NULLABLETIMESTAMPEND" IS NULL)))
    AND (ROWNUM <= (1)
)


-- p__linq__0: '7/11/2018 11:23:00' (Type = Date, IsNullable = false)

-- p__linq__1: '7/11/2018 11:23:00' (Type = Date, IsNullable = false)

I tired using the DbFunctions.AddMilliseconds function, and the DbFunctions.CreateDateTime function in concert with the DbFunctions.AddMilliseconds function. Both these attempts caused exceptions to be thrown due to the improper syntax of the produced queries.

I was able to work around this issue by creating a function in the database that parses a string into a timestamp, and then reference it inside the query by using the EntityFramework.CodeFirstStoreFunctions package on NuGet, a predecessor of which was mentioned in this question . Note that if the column is nullable, the DateTime has to be a DateTime?, or it will cast the result of the function to a date.

My database function:

CREATE OR REPLACE FUNCTION DATETIME_TO_TIMESTAMP 
(
  STR IN VARCHAR2 
) RETURN TIMESTAMP AS 
BEGIN
  RETURN to_timestamp(str, 'YYYY-MM-DD HH24:MI:SS.FF');
END DATETIME_TO_TIMESTAMP;

The corresponding function in C#:

class Funcs
{
    [DbFunction("CodeFirstDatabaseSchema", "DATETIME_TO_TIMESTAMP")]
    public static DateTime? Timestamp(string dateTimeToConvert)
    {
        // Implementation not required
        throw new NotSupportedException();
    }
}

Registering the functions during OnModelCreating:

modelBuilder.Conventions.Add(new FunctionsConvention<TestDbContext>(schemaName));
modelBuilder.Conventions.Add(new FunctionsConvention(schemaName, typeof(Funcs)));

And, finally, the revised query:

string queryTimestamp = queryTime.ToString("yyyy-MM-dd HH:mm:ss.FFFFFFF");
EntityWithTimestamps ent = Db.TableOfEntitiesWithTimestamps
    .Where(ex => (
           (ex.NullableTimestampStart <= Funcs.Timestamp(queryTimestamp)
           && (ex.NullableTimestampEnd > Funcs.Timestamp(queryTimestamp) 
           || (ex.NullableTimestampEnd == null)
        )
    ).FirstOrDefault();

I feel as though this is a bit more complex than it needs to be. Does anyone know of a better way to get Oracle EF to use a timestamp value in LINQ queries?

I also tried modifying my application's app.config file. Oracle.ManagedDataAccess.Client actually allows some control of how numbers get mapped from .NET objects to Oracle types and the precision of those Oracle typed. This is accomplished in the EdmMappings section of the configuration file. Unfortunately, in my attempts, specifying that DateTime .NET objects should be mapped to timestamp objects of a particular precision in Oracle did not have any effect.

Here is a link to a solution that runs through all of my examples.