Issues with DbFunctions.CreateDateTime

Issues with DbFunctions.CreateDateTime

Postby efcoyote » Thu 15 Jan 2015 08:49

I have some simple linq queries, which results in illegal SQL code.

Code: Select all
var query = from e in base.Context.Event
            let startTime = e.TimeStart ?? DateTime.Now
            select DbFunctions.CreateDateTime(startTime.Year, startTime.Month, startTime.Day, startTime.Hour, startTime.Minute, startTime.Second);

ORA-30076: invalid extract field for extract source

Also the using of OracleFunctions-method will not help:

Code: Select all
var query = from e in base.Context.Event
            let startTime = e.TimeStart ?? OracleFunctions.CurrentTimestamp().Value
            select DbFunctions.CreateDateTime(startTime.Year, startTime.Month, startTime.Day, startTime.Hour, startTime.Minute, startTime.Second);

ORA-01830: date format picture ends before converting entire input string

I try also other DbFunctions-methods and OracleFunctions-method regarding to datetime but getting all kinds of Oracle-exceptions. Seems to be that these functions are not tested at all.

PS: EntityFramework 6.1.1 / Devart Oracle 8.4.215.0 / Oracle Server 11g (11.2.0.3.0)
efcoyote
 
Posts: 5
Joined: Tue 19 Aug 2014 12:22

Re: Issues with DbFunctions.CreateDateTime

Postby Shalex » Fri 16 Jan 2015 14:41

We can reproduce the "ORA-30076: invalid extract field for extract source" error only in this case with dotConnect for Oracle v8.4.333:
Code: Select all
var query = from e in base.Context.Event
            // let startTime = e.TimeStart ?? DateTime.Now
            let startTime = DateTime.Now
            select DbFunctions.CreateDateTime(startTime.Year, startTime.Month, startTime.Day, startTime.Hour, startTime.Minute, startTime.Second);

We will notify you when the issue is fixed.

efcoyote wrote:
Code: Select all
var query = from e in base.Context.Event
            let startTime = e.TimeStart ?? DateTime.Now
            select DbFunctions.CreateDateTime(startTime.Year, startTime.Month, startTime.Day, startTime.Hour, startTime.Minute, startTime.Second);
ORA-30076: invalid extract field for extract source

Code: Select all
var query = from e in base.Context.Event
            let startTime = e.TimeStart ?? OracleFunctions.CurrentTimestamp().Value
            select DbFunctions.CreateDateTime(startTime.Year, startTime.Month, startTime.Day, startTime.Hour, startTime.Minute, startTime.Second);
ORA-01830: date format picture ends before converting entire input string

We cannot reproduce these problems with dotConnect for Oracle v8.4.333. Please tell us
a) the value of the e.TimeStart property when you are running the code
b) the datatype of the column in database which corresponds to e.TimeStart
c) enable dbMonitor and specify the corresponding SQL when errors occur
Shalex
Devart Team
 
Posts: 7606
Joined: Thu 14 Aug 2008 12:44

Re: Issues with DbFunctions.CreateDateTime

Postby efcoyote » Tue 20 Jan 2015 16:19

a) the critical rows are when e.TimeStart is null (date expression is used)
b) the datatype of the column is "DATE"
c) here are the corresponding SQL outputs from Context.Database.Log without "e.TimeStart ?? ":

first query
Code: Select all
SELECT
TO_TIMESTAMP(EXTRACT(YEAR FROM (CURRENT_DATE)) || '-' || EXTRACT(MONTH FROM (CURRENT_DATE)) || '-' || EXTRACT(DAY FROM (CURRENT_DATE)) || ' ' || EXTRACT(HOUR FROM (CURRENT_DATE)) || ':' || EXTRACT(MINUTE FROM (CURRENT_DATE)) || ':' || TO_NUMBER(EXTRACT(SECOND FROM (CURRENT_DATE))), 'yyyy-mm-dd hh24:mi:ss') AS C1
FROM DUAL

second query
Code: Select all
SELECT
TO_TIMESTAMP(EXTRACT(YEAR FROM (CURRENT_TIMESTAMP)) || '-' || EXTRACT(MONTH FROM (CURRENT_TIMESTAMP)) || '-' || EXTRACT(DAY FROM (CURRENT_TIMESTAMP)) || ' ' || EXTRACT(HOUR FROM (CURRENT_TIMESTAMP)) || ':' || EXTRACT(MINUTE FROM (CURRENT_TIMESTAMP)) || ':' || TO_NUMBER(EXTRACT(SECOND FROM (CURRENT_TIMESTAMP))), 'yyyy-mm-dd hh24:mi:ss') AS C1
FROM DUAL
efcoyote
 
Posts: 5
Joined: Tue 19 Aug 2014 12:22

Re: Issues with DbFunctions.CreateDateTime

Postby Shalex » Fri 23 Jan 2015 11:50

We have reproduced both errors. We will investigate the issues and notify you about the result.
Shalex
Devart Team
 
Posts: 7606
Joined: Thu 14 Aug 2008 12:44

Re: Issues with DbFunctions.CreateDateTime

Postby Shalex » Thu 29 Jan 2015 15:53

The bug with using the Hour, Minute, and Second canonical functions with DATE arguments is fixed in the latest (8.4.342) build of dotConnect for Oracle.
Shalex
Devart Team
 
Posts: 7606
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle