ORA-00904 "Extent1"."SAMIDX"

CConnes · Post by **CConnes** » Fri 10 Feb 2012 15:46

Hi,
I've an error 904 with the query generated by devArt for a linq query such as :
var ar = "zeze".Split(' ');

Samples.Where (s => s.CatalogId == 21 && s.InnerType == 2 &&
s.LabelValues.Any(lv => lv.Label.Name.Equals("FamilyCode") && ar.Any(k => lv.InnerValue.Contains(k)))
).Count();

The generated sql is :
SELECT Count(1) AS A1
FROM SLESAMPLE "Extent1"
WHERE (("Extent1".CATIDX = 21) AND ("Extent1".SAMTYPE = 2)) AND ( EXISTS (SELECT
1 AS C1
FROM ( SELECT
"Extent2".LBVVALUE AS LBVVALUE,
"Extent3".LABNAME AS LABNAME
FROM SLELABVALUE "Extent2"
INNER JOIN SLELABEL "Extent3" ON "Extent2".LABIDX = "Extent3".LABIDX
WHERE "Extent1".SAMIDX = "Extent2".SAMIDX
) "Project1"
WHERE ("Project1".LABNAME = 'FamilyCode') AND ( EXISTS (SELECT
1 AS C1
FROM ( SELECT 1 FROM DUAL) "SingleRowTable1"
WHERE (INSTR("Project1".LBVVALUE, 'zeze')) > 0
))
))

on oracle 11.
I've a similar problem on mysql 5.5

I've found this in the FAQ : http://www.devart.com/dotconnect/oracle/faq.html#q118
But there is no workaround listed.

Is a correction scheduled ?
If not, what workaround can i use ? The linq query has to be the same for MsSql with microsoft provider & Oracle/Mysql with DevArt providers.

I can provide a little sample project if there is a way to upload.
My version of Devart is 6.30.172, i made a try with 6.60.283.0 but nothing has changed

Thanks.

CConnes

Shalex · Post by **Shalex** » Tue 14 Feb 2012 16:44

The only workaround is to reconstruct the query to avoid the situation which is described at http://www.devart.com/dotconnect/oracle/faq.html#q118.

CConnes · Post by **CConnes** » Wed 15 Feb 2012 08:25

Shalex wrote:The only workaround is to reconstruct the query

How do i do this ?

Shalex · Post by **Shalex** » Fri 17 Feb 2012 17:15

Please send us a small test project with the corresponding DDL/DML script to reproduce the issue. We will try to find a workaround for your case.

CConnes · Post by **CConnes** » Tue 21 Feb 2012 13:12

Hi,
I've just posted a test project name TestVisitor from the contact page.
You should be able to recreate the 3 database sfrom the edmx without any problem.

Thanks for your help.

Shalex · Post by **Shalex** » Tue 28 Feb 2012 17:00

1. Try using more optimal LINQ to Entities query which produces smaller, faster SQL without OUTER APPLY:

Code: Select all

static Expression> queryOracle = s => s.CATIDX == 21 && s.SAMTYPE== 2 && 
    s.SLELABVALUE.Any(lv => lv.SLELABEL.LABNAME.Equals("FamilyCode") && ar.Contains(lv.LBVVALUE));

instead of

Code: Select all

static Expression> queryOracle = s => s.CATIDX == 21 && s.SAMTYPE== 2 && 
    s.SLELABVALUE.Any(lv => lv.SLELABEL.LABNAME.Equals("FamilyCode") && ar.Any(k => lv.LBVVALUE.Contains(k)));

You can change the query for MySQL correspondingly.

2. Usage of SQL Server, MySQL, and Oracle simultaneously in one application can be implemented in the following three alternative ways:
2.1. Creating repositories for 3 models
2.2. Using mapping which is loaded dynamically:
2.2.1. With 3 *.edmx. Only one of them generates the code of model and entity classes, others two are used as sources of XML resources (example: http://www.devart.com/efquerysamples.zip)
2.2.2. Only one model and database independent fluent mapping: http://www.devart.com/blogs/dotconnect/ ... plate.html

CConnes · Post by **CConnes** » Wed 29 Feb 2012 08:33

Hi Shalex,

For the point 1 : sorry but the two expressions are not equivalent : they do not produce the same result :
var ar = "abc cde fgh".Split();
var lbvvalue = "wxc qsd cde";
ar.Any (k => lbvvalue.Contains(k)) != ar.Contains(lbvvalue);

for the 2 point: thanks but as i wrote it in the file, this is already done but i didn't report it in the test project to keep it simple

Shalex · Post by **Shalex** » Wed 29 Feb 2012 16:44

We are investigating the issue.