linqConnect for oracle producing invalid SQL

[creaseypaul]

Using dotConnect for Oracle and linqConnect, the following linq:

Code: Select all

context.MyTable.OrderBy(x.Col1).Select(x.Col1 + ":" + x.Col1).Distinct()

Produced a sql statement like this:

Code: Select all

SELECT DISTINCT t1."Col1" || ':' || t1."Col1" AS c1
   FROM my_schema.MyTable t1
ORDER BY t1."Col1"
;

This is invalid and causes:

ORA-01791 not a SELECTed expression

Cause: There is an incorrect ORDER BY item. The query is a SELECT DISTINCT query with an ORDER BY clause. In this context, all ORDER BY items must be constants, SELECT list expressions, or expressions whose operands are constants or SELECT list expressions.

Action: Remove the inappropriate ORDER BY item from the SELECT list and retry the statement.

Entity developer version 3.50.134[/quote]

[StanislavK]

Thank you for the report, we have reproduced the issue. We will investigate it and post here when it is fixed.

However, could you please specify the purpose of using the OrderBy statement before the Select one? As the ordering will be performed in a subquery (i.e., before applying the 'distinct' keyword) in case the LINQ query is translated properly, the outer query may return data in some other order. If you do want to sort the result set, please try using the OrderBy method after the Distinct one:

Code: Select all

context.MyTable
  .Select(x.Col1 + ":" + x.Col1)
  .Distinct()
  .OrderBy(x.Col1);