Page 1 of 1

dotConnect for Oracle Entity Model generates inefficient sql

Posted: Thu 25 Mar 2010 22:07
by ckelley
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?

Posted: Fri 26 Mar 2010 09:46
by AndreyR
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.