dotConnect for Oracle Entity Model generates inefficient sql

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
Posts: 5
Joined: Wed 24 Mar 2010 04:10
Location: Berkeley,CA USA

dotConnect for Oracle Entity Model generates inefficient sql

Post by 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?

Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by 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.

Post Reply