linqConnect for oracle producing invalid SQL

Discussion of open issues, suggestions and bugs regarding LinqConnect – Devart's LINQ to SQL compatible ORM
Post Reply
Posts: 11
Joined: Mon 14 Feb 2011 15:56

linqConnect for oracle producing invalid SQL

Post by 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]

Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by 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

  .Select(x.Col1 + ":" + x.Col1)

Post Reply