In clause

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
edstaffin
Posts: 43
Joined: Mon 13 Oct 2008 13:23

In clause

Post by edstaffin » Tue 14 Oct 2008 17:40

In normal linq2sql I can create an in clause using syntax similar to:
Dim qry = From x In db.YourTable Where (From y In _
db.Table2 Where y.field = 123 _
Select y.field).Contains(x.field) _
Select New With {x.field}

However when I try something similar to this using your product I get the following error:

"Method 'CompareObjectEqual' has no supported translation to SQL."

Do you no support this?
Also, do you now or will you be supporting the SqlMethods object calls? In particular, I am interested in SqlMethods.Like().
Thanks ... Ed

Shalex
Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Wed 15 Oct 2008 11:03

SqlMethods object calls are not supported now. We are planning to implement this functionality in the future.

edstaffin
Posts: 43
Joined: Mon 13 Oct 2008 13:23

Post by edstaffin » Wed 15 Oct 2008 15:54

What about the rest of my question?

Shalex
Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Thu 16 Oct 2008 09:55

We cannot reproduce the "Method 'CompareObjectEqual' has no supported translation to SQL." error using your LINQ expression. Please tell us, what are the fields types (x.field, y.field)?

pokaragat
Posts: 23
Joined: Mon 15 Sep 2008 21:09

Post by pokaragat » Mon 20 Oct 2008 17:58

I have this calls:


decimal[] included = new decimal[]{111111,222222};

var dataEntryQuery = Entities.DATA_ENTRY.Where( c => included.Contains(c.DATA_ENTRY_ID) );

note that c.DATA_ENTRY_ID is defined as decimal.

It threw an exception:
System.NotSupportedException

LINQ to Entities does not recognize the method 'Boolean Contains[String](System.Collections.Generic.IEnumerable`1[System.String], System.String)' method, and this method cannot be translated into a store expression.

What am I doing wrong?

pokaragat
Posts: 23
Joined: Mon 15 Sep 2008 21:09

Post by pokaragat » Thu 23 Oct 2008 13:34

What I should have asked:

How to implement the "in" clause as:

SELECT * FROM CUSTOMER T WHERE T.NAME IN ('JOHN', 'MARY');

Shalex
Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Mon 27 Oct 2008 12:36

We have tested dotConnect for Oracle Beta version 5.00.
The following code is working fine (for table Emp):

Code: Select all

string[] names = new string[] { "SMITH", "ALLEN" };
var dataEntryQuery = db.EMPs.Where(c => names.Contains(c.ENAME));

pokaragat
Posts: 23
Joined: Mon 15 Sep 2008 21:09

I tried with 5.0 and here's what I got. Any clue?

Post by pokaragat » Mon 17 Nov 2008 15:55

Test method CaridianBCT.Vcl.Domain.BusinessObjects.IntegrationTest.DataTestt.TestMethod9 threw exception: System.NotSupportedException: LINQ to Entities does not recognize the method 'Boolean Contains[String](System.Collections.Generic.IEnumerable`1[System.String], System.String)' method, and this method cannot be translated into a store expression..


System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.DefaultTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
Translate(ExpressionConverter parent, Expression linq)
System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
System.Data.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input)
System.Data.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input, DbExpressionBinding& binding)
System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, DbExpression& source, DbExpressionBinding& sourceBinding, DbExpression& lambda)
System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod)
System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
Translate(ExpressionConverter parent, Expression linq)
System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
System.Data.Objects.ELinq.ExpressionConverter.Convert()
System.Data.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forMergeOption)
GetResults(Nullable`1 forMergeOption)
GetEnumerator()
CaridianBCT.Vcl.Domain.BusinessObjects.IntegrationTest.DataTestt.TestMethod9() in D:\VCL\Projects\ASM\Main\IntegrationTest\CaridianBCT.Vcl.Domain.BusinessObjects.IntegrationTest\DataTestt.cs: line 200

pokaragat
Posts: 23
Joined: Mon 15 Sep 2008 21:09

Post by pokaragat » Mon 17 Nov 2008 16:05

Opppss....

I tried the ff code:

[TestMethod]
public void TestMethod10()
{
string[] decs = new string[]{ "HB_SCALING", "HB_LOSS_CORRECTION" };

var query = entities.SYSTEM_CONFIGURATION_TYPE.Where(c=> decs.Contains(c.SYSTEM_CONFIGURATION_NAME));

foreach (SYSTEM_CONFIGURATION_TYPE configType in query)
.....

I got the ff errors:
Test method CaridianBCT.Vcl.Domain.BusinessObjects.IntegrationTest.DataTestt.TestMethod10 threw exception: System.NotSupportedException: LINQ to Entities does not recognize the method 'Boolean Contains[String](System.Collections.Generic.IEnumerable`1[System.String], System.String)' method, and this method cannot be translated into a store expression..


System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.DefaultTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
Translate(ExpressionConverter parent, Expression linq)
System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
System.Data.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input)
System.Data.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input, DbExpressionBinding& binding)
System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, DbExpression& source, DbExpressionBinding& sourceBinding, DbExpression& lambda)
System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod)
System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
Translate(ExpressionConverter parent, Expression linq)
System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
System.Data.Objects.ELinq.ExpressionConverter.Convert()
System.Data.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forMergeOption)
GetResults(Nullable`1 forMergeOption)
GetEnumerator()
CaridianBCT.Vcl.Domain.BusinessObjects.IntegrationTest.DataTestt.TestMethod10() in D:\VCL\Projects\ASM\Main\IntegrationTest\CaridianBCT.Vcl.Domain.BusinessObjects.IntegrationTest\DataTestt.cs: line 213

pokaragat
Posts: 23
Joined: Mon 15 Sep 2008 21:09

Post by pokaragat » Mon 17 Nov 2008 16:36

The above code was done on 5.0 dotconnect professional released version.

Post Reply