.NET to Oracle default type mapping

[thakkarmayank]

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 ... pping.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

[Shalex]

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 ... tions.html.

[thakkarmayank]

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

[Shalex]

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?

[thakkarmayank]

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

[Shalex]

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.

[thakkarmayank]

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

[Shalex]

It will take several weeks to release the build with the fix. We will notify you about the progress.

[thakkarmayank]

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?

[Shalex]

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.

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]

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.