Page 1 of 1
Order by and Except
Posted: Mon 20 Jun 2011 09:55
by 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)?
Posted: Mon 20 Jun 2011 12:52
by AndreyR
Thank you for the report, I have reproduced the issue.
I will let you know about the results of our investigation.
Posted: Thu 23 Jun 2011 10:47
by 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.
Posted: Thu 23 Jun 2011 11:02
by damon.cognito
AndreyR wrote: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
Posted: Thu 23 Jun 2011 11:38
by 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);
Posted: Thu 23 Jun 2011 11:54
by damon.cognito
Yup, that's the one; many thanks for your quick help once again.