CODEWITHSUNDEEP
c#oracle-databaselinqdatetimeodp.net
Krant
Krant

Reputation: 101

Dateformat problem in Linq query in C# ODP.NET

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:

Upvotes: 1

Views: 1109

Answers (3)

Krant
Krant

Reputation: 101

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.

Upvotes: 0

Kostarsus
Kostarsus

Reputation: 37

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

Upvotes: -1

Harald Coppoolse
Harald Coppoolse

Reputation: 30474

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.

Upvotes: 0

Related Questions