Devart Forums

ckelley

I assume that EF v4 is bundled with .NET 4.0 whose release date is 12 April 2010. Good timing.

ckelley

We're using dotConnect for Oracle 5.35.79 and the Entity Framework to support a paged Silverlight DataGrid. The application executes to queries against an Oracle view: the first to get the virtual item count for the pager and the second to get the details.

The sql generated for the virtual item count with two filter conditions looks like this:

Code: Select all

SELECT 
"Project1".C1 AS C1
FROM   ( SELECT 1 FROM DUAL) "SingleRowTable1"
LEFT OUTER JOIN  (SELECT 
	"GroupBy1".A1 AS C1
	FROM ( SELECT Count(1) AS A1
		FROM (SELECT 
      MyView.Column1 AS Column1, 
      MyView.Column2 AS Column2, 
      MyView.Column3 AS Column3, 
      MyView.DATE_VIEWED AS DATE_VIEWED, 
      MyView.USERID AS USERID, 
      FROM MyView MyView) "Extent1"
		WHERE (("Extent1".USERID = :p__linq__6) 
		AND ("Extent1".DATE_VIEWED >= ( CAST(:p__linq__8 AS TIMESTAMP(9)))))
	)  "GroupBy1" ) "Project1" ON 1 = 1

The performance of this query was so bad we had to materialize the view. I'm not an Oracle jock, but I believe this count query is inefficient because the view is nested inside a sub-select. Oracle is therefore unable to apply the where conditions directly to the view and must first get the entire view before filtering it. The sql generated is way more complicated that is necessary.

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?

ckelley

Thank you for saving my bacon, Andrey.

In addition to inserting Schema="xxx" properties in the .edmx file, dotConnect for Oracle also qualified the view names with the schema name in the sql statements generated to read the views. After removing the schema="xxx" properties and the schema qualifiers from the view names in the .edmx sql, everything worked as expected.

I suppose that the schema needs to be included in cases where views from multiple schemas are represented in the same Entity Model.

ckelley

We're using dotConnect for Oracle 5.35.79.0 to access two views and a table via an Entity Framework model. We're connecting to Oracle 11g in test and 10g in production. Test is working great.

We copied the views and table to a production schema and changed the connectionstring for the entity model to the userID and password of the production schema owner. When we fired up the production application, we got "ORA-00942: table or view does not exist" errors when attempting to access any of the views or table. The production connection string credentials were good enough to login to Oracle, but not good enough to see the views and table.

We then changed the production Entity Model connection string in the web.config file to point back to the test database. The production instance was able to access the test db objects without a problem.

My Oracle guy insists that the production string credentials are that of the production schema owner and therefore should be able to see the views and table. He is pointing his figure at dotConnect. If we were using Sql Server, I'd turn on the profiler and find out what is really going on. My Oracle guy says that Oracle doesn't have a similar profiler.

Does DevArt provide any way to see the conversation between dotConnect and Oracle? Any ideas? Help!

Devart Forums

Search found 5 matches

dotConnect/Oracle Entity Model generates slow count sql

dotConnect for Oracle Entity Model generates inefficient sql

Removing the Schema property and Schema qualifiers worked

dotConnect for Oracle can't see views and table