dotConnect for Oracle Entity Model generates inefficient sql

dotConnect for Oracle Entity Model generates inefficient sql

Postby ckelley » Thu 25 Mar 2010 22:07

Sorry about the accusation in the subject. It may well be something that I can control from LINQ or the .edmx file. Please read on...

We're using dotConnect for Oracle 5.35.79 and the Entity Framework. We're seeing sql generated that is not optimal and we'd like to know how to control it. To follow are two examples:

When it generates a sql for a LINQ statement that includes a where condition like (UserID == 'MYUSERID'), the generated sql translates this into ('myuserid' = LOWER(UserID). This defeats the case-sensitive index for UserID, a VARCHAR2 column.

When a LINQ statement compares an Oracle Date column with a .NET DateTime cutoff value like (DATE_VIEWED >= cutoff), dotConnect generates
(DATE_VIEWED >= (CAST('25-MAR-10' AS TIMESTAMP(9))))). Won't this also defeat the index on DATE_VIEWED? Wouldn't it be better to cast it as DATE?

Is there any way to control these inefficiencies from either LINQ or the .edmx?
ckelley
 
Posts: 5
Joined: Wed 24 Mar 2010 04:10
Location: Berkeley,CA USA

Postby AndreyR » Fri 26 Mar 2010 09:46

Date is converted to timestamp in order not to lose milliseconds contained by System.DateTime.
I would ask you to send a simple project reproducing the issue, this will speed up the investigation.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16


Return to dotConnect for Oracle