linqConnect for oracle producing invalid SQL

linqConnect for oracle producing invalid SQL

Postby creaseypaul » Mon 16 Jan 2012 17:36

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]
creaseypaul
 
Posts: 11
Joined: Mon 14 Feb 2011 15:56

Postby StanislavK » Tue 17 Jan 2012 09:45

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);
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48


Return to LinqConnect (LINQ to SQL support)