2

I'm using Linq to query an EF6 context representing an Oracle database. I'm trying to select rows from a table based on a TIMESTAMP field, which stores datetime up to fractions of a second. It is the most bog-standard query you can write, yet it doesn't work.

data = await dbContext.MyTable.Where(x => x.Timestamp > LastTimestamp).ToArrayAsync();

This query seems to return all data from the table, regardless of the value of the timestamp. When I step through the code and inspect the properties of each entity returned, they have the correct data, with the exception that they shouldn't be part of the result set.

The entity specifies the Timestamp property as a DateTime object, is this the correct representation for the Oracle data type TIMESTAMP?

I think the issue is that the high resolution timestamp ends up always being slightly ahead of the datetime that I pass in, which has been truncated by Linq. Is there a way to handle this?

Some related questions:

Can't compare 2 dates in oracle correctly

EF6 Oracle TimeStamp & Date

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

Selim Yildiz
  • 5,254
  • 6
  • 18
  • 28
Scott Oliver
  • 507
  • 5
  • 19
  • Can you include the value of `LastTimestamp` and the values of `Timestamp` that you end up getting from the query and which ones you expect to get filtered out. – juharr Jun 03 '20 at 17:40
  • Are you getting a string or a DataTime object? – jdweng Jun 03 '20 at 17:46
  • 1
    Same problem here: https://stackoverflow.com/questions/57765675/entity-framework-oracle-timestamp, I wrote to Oracle team and they accepted that it is a bug. You can check my issue on : https://community.oracle.com/thread/4288922, wait for the new version of `Oracle.ManagedDataAccess.EntityFramework` it will be fixed. – Selim Yildiz Jun 03 '20 at 18:22
  • @juharr For example, if I query for rows with a timestamp greater than "2020-06-01T17:08:01.2352310", the result set includes rows with that timestamp. Adding logging to the context reveals that the parameter in the generated SQL is "01/06/2020 17:08:01". – Scott Oliver Jun 04 '20 at 09:21
  • @SelimYıldız Good to know it'll be fixed, thanks. Did you implement a workaround? In my specific use case, I could imagine doing something like incrementing to the next whole second but that's only because I don't need the extra precision. – Scott Oliver Jun 04 '20 at 09:24
  • As a workaround you can use SQLRawQuery like :`dbContext.Database.SqlQuery("SELECT * FROM Mytable WHERE Timestamp > :LastTimestamp", new OracleParameter("LastTimestamp", OracleDbType.TimeStamp, LastTimestamp, System.Data.ParameterDirection.Input)); ` , please let me know if it helps I can provide an answer. – Selim Yildiz Jun 04 '20 at 10:16
  • @SelimYıldız Feel free to add that as an answer – Scott Oliver Jun 04 '20 at 11:46
  • I have added an answer, it worked for us. – Selim Yildiz Jun 04 '20 at 12:58

1 Answers1

1

As I pointed out in comments section, I encountered the same problem:

Entity Framework Oracle Timestamp

I wrote to Oracle team and they accepted that it is a bug. You can check my issue on : https://community.oracle.com/thread/4288922, you need to wait for the new version of Oracle.ManagedDataAccess.EntityFramework it will be fixed.

However, as a workaround you can use SQLRawQuery, it worked for us.

dbContext.Database.SqlQuery("SELECT * FROM Mytable WHERE Timestamp > :LastTimestamp", new OracleParameter("LastTimestamp", OracleDbType.TimeStamp, LastTimestamp, System.Data.ParameterDirection.Input)).ToList();
Selim Yildiz
  • 5,254
  • 6
  • 18
  • 28