Problem of With Cast To_Number With EF 1.0

Problem of With Cast To_Number With EF 1.0

Postby Eblanchard » Wed 10 Aug 2011 10:08

Hello,

Y have a problem with DotConnect for Oracle with Entity Framework 1.0. All Query generated by EF used Cast With "TO_NUMBER" and it's a big problem for me because my Indexes of Oracle are not Used with ths CAST and y have always some Full Access TABLE in all my queries. Of Course the performence of my application are catastrophic.

Exemple of query :

SELECT --allcolumns --
FROM RH "Extent1"
INNER JOIN RH2 "Extent2"
ON (TO_NUMBER("Extent1".RHID)) = (TO_NUMBER("Extent2".RHID))
LEFT OUTER JOIN CONTRAT "Extent3"
ON (TO_NUMBER("Extent1".ID_CONTRAT)) = (TO_NUMBER("Extent3".ID_CONTRAT))
---- other join ----
WHERE ((("Extent1".DATE_RH >= :p__linq__0) AND ("Extent1".DATE_RH <= :p__linq__1)) AND ((TO_NUMBER(TO_NUMBER("Extent2".ID_ELEMENT_STRUCTURE))) = :p__linq__2)) AND ("Extent4".ID_ETT IN (1195,1020,1640,1023,1488,1021))
) "Project1

In my model EF, i try yo use "Int" or "Decimal" but there is no change, there is always a To_Number Cast in the SQL Query.

Did some body have an idea for that problem ?

PS : It's impossible for me to change this indexes in Oracle because all of my storedProcedures used this indexes. We Migrate Progressively the application From ODP.NET To DotConnect for Oracle.

Thanks a lot for your response and Have a nice Day.

Eric.
Eblanchard
 
Posts: 1
Joined: Wed 10 Aug 2011 09:28

Postby Shalex » Wed 10 Aug 2011 11:35

Thank you for your report. We will investigate the issue and notify you about the results as soon as possible.
Shalex
Devart Team
 
Posts: 7608
Joined: Thu 14 Aug 2008 12:44

Postby Shalex » Thu 11 Aug 2011 10:13

The bug with redundant CASTs when working with NUMBERs is fixed. Look forward to the next build of dotConnect for Oracle (in 2-3 weeks). We will post here when it is available for download.

Workaround (can be applied in the current build).

We can reproduce the problem in the following way:
1. Create model for two tables which have the one-to-many relation. The type of the columns which take part in the relation is decimal or double (in SSDL).
2. Change the .NET type of these columns: Decimal -> Int32 in the designer (only CSDL is modified in this case).
3. The query like
Code: Select all
var query = db.Product_Categories.Include("Products").ToList();

generates CASTs.

How to avoid this behaviour: if you modify the types of properties in CSDL, change correspondingly the types of properties in SSDL (it can be done in the design time using Entity Developer). We have synonyms for each .NET type: http://www.devart.com/dotconnect/oracle/docs/?DataTypeMapping.html. If types in SSDL and CSDL are corresponding, CASTs are not generated in the current build as well.
Shalex
Devart Team
 
Posts: 7608
Joined: Thu 14 Aug 2008 12:44

Postby Shalex » Wed 07 Sep 2011 11:12

New version of dotConnect for Oracle 6.50 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 valid subscription only): http://secure.devart.com/ .
For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=21942 .
Shalex
Devart Team
 
Posts: 7608
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle