Dateformat problem in Linq query in C# ODP.NET

Question

Currently I have a model in my application using the ODP.NET from Oracle. It is possible for me to using linq-queries now.

The program does execute the following:

DateTime searchDate = DateTime.Now.AddDays(-days);
oracleShipments = oracleEntities.Shipments.Where(s => consignorCodes.Contains(s.CONSIGNOR) && s.UNLOADINGTIMEEND > searchDate).ToList();

s.UNLOADINGTIMEEND = DateTime? searchDate = DateTime

The code is working on my computer, it returns me a list with the correct Shipments. All is working except for another computer, there it will give me the following inner exception:

Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-01843: Geen geldige maand. bij OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone) bij OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, OracleDataReaderImpl& rdrImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[] scnForExecution, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Int64& internalInitialLOBFS, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, IEnumerable`1 adrianParsedStmt, Boolean isDescribeOnly, Boolean isFromEF) bij Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior) bij Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior) bij System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed) bij System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext) bij System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)

Please note: Geen geldig maand. means Not a valid month. Looking back to the Linq-query: s.UNLOADINGTIMEEND > searchDate are both a DateTime format. Comparing them would be an easy task to say. I think that it has to do with the NLS-dateformat.

The NLS_DATE_FORMAT of the server is DD-MON-RR.

Is there a possibility to get this working on all computers? I prefer to use Linq over hardcoded typed query.

The linq-query output is

SELECT  "Extent1"."SHIPMENT" AS "SHIPMENT",  "Extent1"."CONSIGNOR" AS
"CONSIGNOR",  "Extent1"."UNLOADINGCOMPANY" AS "UNLOADINGCOMPANY", 
"Extent1"."UNLOADINGCITY" AS "UNLOADINGCITY", 
"Extent1"."UNLOADINGCOUNTRY" AS "UNLOADINGCOUNTRY", 
"Extent1"."UNLOADINGPLANNEDSTART" AS "UNLOADINGPLANNEDSTART", 
"Extent1"."UNLOADINGPLANNEDEND" AS "UNLOADINGPLANNEDEND", 
"Extent1"."UNLOADINGREALIZEDSTART" AS "UNLOADINGREALIZEDSTART", 
"Extent1"."UNLOADINGREALIZEDEND" AS "UNLOADINGREALIZEDEND", 
"Extent1"."UNLOADINGACTUALSTART" AS "UNLOADINGACTUALSTART", 
"Extent1"."UNLOADINGACTUALEND" AS "UNLOADINGACTUALEND", 
"Extent1"."UNLOADINGTIMESTART" AS "UNLOADINGTIMESTART", 
"Extent1"."UNLOADINGTIMEEND" AS "UNLOADINGTIMEEND",  "Extent1"."Fixed"
AS "Fixed" FROM (SELECT  "V_PERFORMANCETOOL"."SHIPMENT" AS
"SHIPMENT",  "V_PERFORMANCETOOL"."CONSIGNOR" AS "CONSIGNOR", 
"V_PERFORMANCETOOL"."UNLOADINGCOMPANY" AS "UNLOADINGCOMPANY", 
"V_PERFORMANCETOOL"."UNLOADINGCITY" AS "UNLOADINGCITY", 
"V_PERFORMANCETOOL"."UNLOADINGCOUNTRY" AS "UNLOADINGCOUNTRY", 
"V_PERFORMANCETOOL"."UNLOADINGPLANNEDSTART" AS
"UNLOADINGPLANNEDSTART", 
"V_PERFORMANCETOOL"."UNLOADINGPLANNEDEND" AS
"UNLOADINGPLANNEDEND", 
"V_PERFORMANCETOOL"."UNLOADINGREALIZEDSTART" AS
"UNLOADINGREALIZEDSTART", 
"V_PERFORMANCETOOL"."UNLOADINGREALIZEDEND" AS
"UNLOADINGREALIZEDEND", 
"V_PERFORMANCETOOL"."UNLOADINGACTUALSTART" AS
"UNLOADINGACTUALSTART",  "V_PERFORMANCETOOL"."UNLOADINGACTUALEND"
AS "UNLOADINGACTUALEND",  "V_PERFORMANCETOOL"."UNLOADINGTIMESTART"
AS "UNLOADINGTIMESTART",  "V_PERFORMANCETOOL"."UNLOADINGTIMEEND"
AS "UNLOADINGTIMEEND",  "V_PERFORMANCETOOL"."Fixed" AS "Fixed"
FROM "CUSTOMIZATION"."V_PERFORMANCETOOL" "V_PERFORMANCETOOL")
"Extent1" WHERE ((('Company1' = "Extent1"."CONSIGNOR") OR
('Company2' = "Extent1"."CONSIGNOR")) AND
("Extent1"."UNLOADINGTIMEEND" > :p__linq__0))


-- p__linq__0: '13-12-2018 10:27:16' (Type = Date, IsNullable = false)

-- Executing at 20-12-2018 10:27:17 +01:00

-- Completed in 471 ms with result: OracleDataReader

In the Oracle view, the UNLOADINGTIMEEND has the DATA_TYPE = DATE which may be nullable. I double checked and the column of the table (where I get the info on) is also type DATE.

When replacing p__linq__0 with TO_DATE('2018/12/13 11:00:00', 'YYYY/MM/DD HH:MI:SS') and executing the Linq-generated query, I got the same results as in my program.

Additional info:

.NET Framework 4.6.1 (Due to compatibility in our company I'm using)
Oracle.ManagedDataAccess 18.3.0
Oracle.ManagedDataAccess.EntityFramework 18.3.0

What .NET types are `searchDate` and `UNLOADINGTIMEEND`? I guess they are not DateTime? If not, why not? — nvoigt, Dec 20 '18 at 08:45
What is the data type of `UNLOADINGTIMEEND` in the database table? — Wernfried Domscheit, Dec 20 '18 at 08:48
Can you please show the generated SQL statement? (see https://stackoverflow.com/questions/1412863/how-do-i-view-the-sql-generated-by-the-entity-framework/20751723#20751723) — Wernfried Domscheit, Dec 20 '18 at 08:51
@nvoigt s.UNLOADINGTIMEEND = DateTime? searchDate = DateTime — Krant, Dec 20 '18 at 09:38
@Wernfried-Domscheit: I also added the generated SQL statement: ("Extent1"."UNLOADINGTIMEEND" > :p__linq__0) Thanks for this nice tip! — Krant, Dec 20 '18 at 09:38
That's all? `("Extent1"."UNLOADINGTIMEEND" > :p__linq__0)` is not a valid SQL statement. And again, what is the data type of column `UNLOADINGTIMEEND` in database? — Wernfried Domscheit, Dec 20 '18 at 09:54
No, that was (most important) part. I have updated the whole output in the openingspost. The `UNLOADINGTIMEEND` is defined as `DATE` in the database (which may be nullable) — Krant, Dec 20 '18 at 10:03
@Krant date values have no format, they are binary values, both in .NET and Oracle. If you have a problem it means you are using *strings* instead of dates. Does the field have a string instead of a date type perhaps? — Panagiotis Kanavos, Dec 20 '18 at 10:05
What happens when you execute that query using a date-typed oracle variable? What happens when you execute an ADO.NET command with that query? It's always possible there's an "interesting" bug in ODP.NET that somehow translates dates to strings. Or `UNLOADINGTIMEEND`'s type isn't really a date — Panagiotis Kanavos, Dec 20 '18 at 10:07
@PanagiotisKanavos Thanks for thinking along with me! I double checked: the column `UNLOADINGTIMEEND` of the view is from a table where the type is `DATE`. This means UNLOADINGTIMEEND and searchDate are both real dates. — Krant, Dec 20 '18 at 10:27
Also: I pasted the linq-generated code and replaced the variable with to_date(x), results are same in the program on my pc. Only on the other pc is giving me the month-error. (PS: I added this to opening post) — Krant, Dec 20 '18 at 10:27
@Krant that's why I asked if you tried to run the query in different ways. Somehow, something, is converting the `DateTime` value into a string. — Panagiotis Kanavos, Dec 20 '18 at 10:31
@Krant I suspect that somehow, ODP.NET "helpfully" translates the OracleDate that should be passed to the query into a string, using the US date order. You could try changing the NLS_DATE_FORMAT for each session [as shown here](https://docs.oracle.com/en/database/oracle/oracle-data-access-components/18.3/odpnt/featGlobal.html#GUID-F0B0101D-F10A-41CC-B940-6F963D7C286D). It could also be that a registry or ODP.NET setting on *that* computer affects how ODP.NET treats dates — Panagiotis Kanavos, Dec 20 '18 at 11:53
@PanagiotisKanavos You hit the nail. I already found another [StackOverflow](https://stackoverflow.com/questions/14372297/execute-sql-alter-commands-for-every-session-with-entity-framework-5-talking-to) which I implemented and is working!. If you add your comment as an Answer, I will be happy to accept that. — Krant, Dec 20 '18 at 13:29

score 0 · Answer 1 · answered Dec 20 '18 at 08:47

Property UnloadingTimeEnd has a DateTime value. I think that this value is not calculated until you execute Get. This makes that you won't recognize the problem until you execute your query.

Something like the following:

class Shipment
{
     private string textUnloadingTimeEnd = ...;

     public DateTime UnloadingTime
     {
          get {return DateTime.Parse(textUnloadingTimeEnd); }
     }
}

To find the real cause: make a simple query where you try to get the unloading times:

var unloadingTimes = oracleEntities.Shipments
    .Select(shipment => shipment.UnloadingTime)
    .ToList();

You will probably see the same problem.

Solution: never ever use strings to represent DateTimes. A DateTime is not a string!

As soon as someone, or some system, somewhere on this planet, even in Eindhoven, delivers a string that is meant to represent a DateTime, convert it immediately to a DateTime.

Advantages are that you can detect and solve any formatting problems immediately. The DateTime will be culture independent.

Keep this DateTime a DateTime as long as possible. Only convert it into a string when you need to communicate to the operator or to an external system that doesn't accept the DateTime.

Again, advantage: only at the moment of representation you know precisely the expected string format. This works even if System A wants in in format yyyy-MM-dd and System B wants it in dd-MMM-yy: you know for which system you are formatting, so you know the expected format.

Hello Harald, you are right about string != datetime! That is why I only compare DateTime with DateTime ;). Both are datetime (I added the searchDate declaration to the post). — Krant, Dec 20 '18 at 09:43
@Krant what about the *table field* though? What is the field's type? The SQL query compares the parameter value against the field's type. Your entity may have a `DateTime` property but that won't help if the field is a `varchar(20)` — Panagiotis Kanavos, Dec 20 '18 at 10:06

score 0 · Accepted Answer · answered Jan 11 '19 at 13:18

The NLS_DATE_FORMAT was wrong. Each time when you open the connection, you have to set it right. You can do it like this:

OracleEntities oracleEntities = new OracleEntities();
oracleEntities.Database.Connection.Open();
oracleEntities.Database.ExecuteSqlCommand("ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-RR'");

That it is why it was not always wrong on all computers. For some computers the NLS_DATE_FORMAT was already correct.

Kostarsus · Answer 3 · 2018-12-20T10:32:11.837

-1

You can use CompareTo of DateTime, but this isn't compatible with LinqToSql so you must call AsEnumerable() before you call the method.

    DateTime searchDate = DateTime.Now.AddDays(-days);
    oracleShipments = oracleEntities.Shipments.Where(s => consignorCodes.Contains(s.CONSIGNOR))
                                              .AsEnumerable()
                                              .Where(s=> s.UNLOADINGTIMEEND.CompareTo(searchDate) > 0)
                                              .ToList();

edited Dec 20 '18 at 10:32

answered Dec 20 '18 at 10:12

Kostarsus

37
5

There's no reason to use `CompareTo` and this code instead of fixing the problem makes it far worse by loading *everything* into memory. LINQ to EF is meant to create a SQL statement. If it can't do that the solution is to either fix it or run the SQL statement directly. Not try to load eg 1M rows to find one day's worth of data – Panagiotis Kanavos Dec 20 '18 at 10:38

Dateformat problem in Linq query in C# ODP.NET

3 Answers3