Order by and Except

[damon.cognito]

When using an Except, the order by gets ignored, i.e. the following structure:

Code: Select all

(from 
   where
   orderby
   select new { }).Except(from where select new {})

Any ideas please (Entityframe work to PostgreSQL)?

[AndreyR]

Thank you for the report, I have reproduced the issue.
I will let you know about the results of our investigation.

[AndreyR]

This sorting is removed by the LINQ to Entities engine as non-significant.
We recommend you to perform this sorting after the Except call.
There is no DBMS that keeps the ordering information (concerning the initial subquery) after the Except call. In particular, PostgreSQL ignores the ORDER BY statement, you can check it using the following example:

Code: Select all

(select * from dept order by deptno desc)
except
(select * from dept where deptno > 20)

The resulting departments will be listed in the ascending order, despite the descending order in the initial subquery.

[damon.cognito]

In particular, PostgreSQL ignores the ORDER BY statement, you can check it using the following example:

Code: Select all

(select * from dept order by deptno desc)
except
(select * from dept where deptno > 20)

Yes that makes perfect sense; in postgresql you would do (given ordering is required on column 1):

Code: Select all

(select * from dept)
except
(select * from dept where deptno > 20)
order by 1 desc

[AndreyR]

Yes, that's correct. So, your initial example should look like:

Code: Select all

(from 
    where 
    orderby 
    select new { }).Except(from where select new {}).OrderBy(entity=>entity.OrderingProperty);

[damon.cognito]

Yup, that's the one; many thanks for your quick help once again.