ErrorLINQ: Table "order-by" with Descending and Table.First
Posted: Tue 25 Nov 2008 06:33
I have the following Linq-query (example):
When i access the result with:
the wrong result row is returned. "field" contains the last row without the "Desc"-Sort.
I watched the generated SQL-Statement in DBMonitor. Linq sends the following SQL to the Database:
The correct statement should be:
Code: Select all
dim result = from p in db.table _
where p.field1 = 'value' _
order by p.field2 Descending, p.field3 Descending
Code: Select all
dim field = result.First.field1
I watched the generated SQL-Statement in DBMonitor. Linq sends the following SQL to the Database:
Code: Select all
SELECT t1.field1, t1.field2, t1.field3
FROM (
SELECT t1.field1, t1.field2, t1.field3, ROW_NUMBER() OVER (ORDER BY t2.field2, t2.field3) AS rnum
FROM table t2
WHERE (t2.field1 = 'value')
ORDER BY t2.field2 DESC, t2.field3 DESC
) t1
WHERE t1.rnum <= 1
Code: Select all
SELECT t1.field1, t1.field2, t1.field3
FROM (
SELECT t1.field1, t1.field2, t1.field3, ROW_NUMBER() OVER (ORDER BY t2.field2 DESC, t2.field3 DESC) AS rnum
FROM table t2
WHERE (t2.field1 = 'value')
ORDER BY t2.field2 DESC, t2.field3 DESC
) t1
WHERE t1.rnum <= 1