CODEWITHSUNDEEP
c#oracle-databaseentity-frameworklinqodp.net
Scott Oliver
Scott Oliver

Reputation: 557

Why is my Linq Oracle DB query not comparing timestamp with datetime correctly?

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?

Upvotes: 2

Views: 645

Answers (1)

Selim Yildiz
Selim Yildiz

Reputation: 5370

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();

Upvotes: 1

Related Questions