.NET to Oracle default type mapping

.NET to Oracle default type mapping

Postby thakkarmayank » Thu 24 Apr 2014 19:11

Hi,
We have been using Devart for dotConnect 6.80 for quite some time now and we just recently discovered a issue.

We have around 200 tables in an ORACLE DB and almost all of them have atleast one DATETIME columns. When we write queries against this (using EF 4.1), like:

DateTime dt = DateTime.Today;
var tmp = (from c in TaskEntities
where c.LastUpdateDate >= dt
select c).ToList();

the SQL generated maps the variable dt to a BIND variable of type TIMESTAMP, which is in accordance with http://www.devart.com/dotconnect/oracle/docs/DatatypeMapping.html.


The issue is: Oracle tries to convert all the column values from DATE to TIMESTAMP, hence causing a spike in CPU usage and less throughput. Is there any way I can override the default mapping of System.DateTime in .Net to DATE in Oracle, instead of TIMESTAMP?

I know that I can convert "dt" to DATE explicitly but I have numerous such queries in the application, scattered all over the place. I am looking for a less intrusive way to achieve the same.

~Mayank
thakkarmayank
 
Posts: 13
Joined: Fri 23 Apr 2010 21:16

Re: .NET to Oracle default type mapping

Postby Shalex » Fri 25 Apr 2014 15:19

Please refer to http://forums.devart.com/viewtopic.php?f=1&t=24890.

The documentation about the UseDateTimeAsDate option is available at http://www.devart.com/dotconnect/oracle/docs/?CodeFirstOptions.html.
Shalex
Devart Team
 
Posts: 7659
Joined: Thu 14 Aug 2008 12:44

Re: .NET to Oracle default type mapping

Postby thakkarmayank » Wed 07 May 2014 20:54

Shalex,
Thanks for responding.

I dont think you got my question. Let me rephrase it.

We are not using "Code First" here. So the question of my DB being created from my conceptual model doesnt arise. We have a DB team which builds the tables for us and we base our conceptual model on it.

I did some more tests and found some interesting results.
I modified my query to:

Code: Select all
DateTime dt = DateTime.Today;
var tmp = (from c in TaskEntities
where c.LastUpdateDate >= dt
&& OracleFunctions.Trunc(c.DoneDate) >=  dt
select c).ToList();


I ran the query (LastUpdateDate and DoneDate both are of type DATE in the db) and captured the output using DBMonitor:
In the parameters section, I see 2 parameters being passed in, both of type TimeStamp.

I ran the same query after upgrading my system to dotConnect 8.30. (I did not do any changes with UseDateTimeAsDate property as you suggested) This time, I got one parameter as Date and other as Timestamp.

So it seems like you have fixed the issue partially, if I dont use OracleFunctions.Trunc, then the variable "dt" is mapped to type DATE in the ORACLE world, as it should be(in version 8.3) but if I do use
OracleFunctions.Trunc, then the variable "dt" is mapped to type TIMESTAMP in the ORACLE world.

So while the latest version fixes the issue, which helps me across 60% of my cases, I will appreciate if the OracleFunctions.Trunc issue if fixed too.

Thanks,
Mayank
thakkarmayank
 
Posts: 13
Joined: Fri 23 Apr 2010 21:16

Re: .NET to Oracle default type mapping

Postby Shalex » Fri 09 May 2014 14:57

thakkarmayank wrote:We are not using "Code First" here. So the question of my DB being created from my conceptual model doesnt arise. We have a DB team which builds the tables for us and we base our conceptual model on it.

You are using an XML mapping, aren't you? If yes, please set the Type of your LastUpdateDate and DoneDate properties in the store (SSDL) part of the model to DATE. You can do this via interface of Entity Developer (for *.edml models) or with XML Editor (for *.edmx). Does this set the type of the parameter in the query to Date?
Shalex
Devart Team
 
Posts: 7659
Joined: Thu 14 Aug 2008 12:44

Re: .NET to Oracle default type mapping

Postby thakkarmayank » Fri 09 May 2014 18:57

Shalex,
We are using xml mapping in emdx and yes, the Type of LastUpdateDate and DoneDate properties in the store (SSDL) part of the model is set to DATE.

The issue is, inspite of both being set to DATE, one parameter goes as DATE and other as TIMESTAMP. The only difference is we are using OracleFunctions.Trunc for one .

~Mayank
thakkarmayank
 
Posts: 13
Joined: Fri 23 Apr 2010 21:16

Re: .NET to Oracle default type mapping

Postby Shalex » Tue 13 May 2014 09:32

thakkarmayank wrote:The issue is, inspite of both being set to DATE, one parameter goes as DATE and other as TIMESTAMP. The only difference is we are using OracleFunctions.Trunc for one .

Thank you for your report. We have reproduced the issue and are investigating it.
Shalex
Devart Team
 
Posts: 7659
Joined: Thu 14 Aug 2008 12:44

Re: .NET to Oracle default type mapping

Postby thakkarmayank » Tue 13 May 2014 14:34

Thanks.
This bug sure did cause us some performance grief over the past 4 years.

Do we have a time line as when will this be fixed?

~Mayank
thakkarmayank
 
Posts: 13
Joined: Fri 23 Apr 2010 21:16

Re: .NET to Oracle default type mapping

Postby Shalex » Thu 15 May 2014 11:01

It will take several weeks to release the build with the fix. We will notify you about the progress.
Shalex
Devart Team
 
Posts: 7659
Joined: Thu 14 Aug 2008 12:44

Re: .NET to Oracle default type mapping

Postby thakkarmayank » Tue 27 May 2014 14:08

Thanks.
We just renewed our subscription and we can not use the new version because of this issue and http://forums.devart.com/viewtopic.php?f=1&t=29564 issue.

Is there a way we can extend our subscription because of the inconvenience caused?
thakkarmayank
 
Posts: 13
Joined: Fri 23 Apr 2010 21:16

Re: .NET to Oracle default type mapping

Postby Shalex » Wed 28 May 2014 14:45

The bug with using OracleFunctions and EntityFunctions against class properties, which are of the DATE type in SSDL, is fixed. We will notify you when the corresponding build of dotConnect for Oracle is available for download.

thakkarmayank wrote:Is there a way we can extend our subscription because of the inconvenience caused?

Please submit your request to our Sales department (sales at devart*com).
Shalex
Devart Team
 
Posts: 7659
Joined: Thu 14 Aug 2008 12:44

Re: .NET to Oracle default type mapping

Postby Shalex » Thu 29 May 2014 14:28

New version of dotConnect for Oracle 8.4 is released!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=1&t=29687.
Shalex
Devart Team
 
Posts: 7659
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle