Ordering missing

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
damon.cognito
Posts: 50
Joined: Wed 22 Jul 2009 09:30

Ordering missing

Post by damon.cognito » Wed 18 Jul 2012 16:12

The follow statement

Code: Select all

decimal i = entity.TblTest.OrderByDescending(ir => ir.StampStart)
                .ThenByDescending(ir => ir.Amount)
                .First(ir => ir.TblTestHead.OIDTestHead == OIDTestHead                  
                     && ir.StampStart <= transDate
                     && ir.Amount <= amount).Amount;
produces the following sql

Code: Select all

SELECT 
"Limit1"."C1",
"Limit1"."StampStart",
"Limit1"."Amount",
"Limit1"."OIDTestHead"
FROM ( SELECT 
	"Extent1"."OIDTest",
	"Extent1"."StampStart",
	"Extent1"."Amount",
	1 AS "C1"
	FROM public."TblTest" AS "Extent1"
	WHERE (("Extent1"."OIDTestHead" = 1) AND ("Extent1"."StampStart" <= '2010-12-17')) AND (( CAST("Extent1"."Amount" AS numeric)) <= 12299.18)
	LIMIT 1 
)  AS "Limit1"
This seems to ignore the ordering - it should produce:

Code: Select all

SELECT 
"Limit1"."C1",
"Limit1"."StampStart",
"Limit1"."Amount",
"Limit1"."OIDTestHead"
FROM ( SELECT 
	"Extent1"."OIDTest",
	"Extent1"."StampStart",
	"Extent1"."Amount",
	1 AS "C1"
	FROM public."TblTest" AS "Extent1"
	WHERE (("Extent1"."OIDTestHead" = 1) AND ("Extent1"."StampStart" <= '2010-12-17')) AND (( CAST("Extent1"."Amount" AS numeric)) <= 12299.18)
	order by "Extent1"."StampStart" desc, "Extent1"."Amount" desc
	LIMIT 1 
)  AS "Limit1"
Using Postgresql 8.4 and dotconnect V5.80. If you need an example project, just let me know.

Shalex
Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Re: Ordering missing

Post by Shalex » Mon 23 Jul 2012 14:51

Here is a generated SQL we have obtained with the latest (6.1.40) version of dotConnect for PostgreSQL.

Code: Select all

                decimal i = entity.Depts.OrderByDescending(ir => ir.Deptno)
                .ThenByDescending(ir => ir.Loc)
                .First(ir => ir.Dname == "a"
                     && ir.Deptno <= 100).Deptno;
produces

Code: Select all

SELECT 
"Extent1".deptno,
"Extent1".dname,
"Extent1".loc
FROM public.dept AS "Extent1"
WHERE ("Extent1".dname = 'a') AND ("Extent1".deptno <= 100)
ORDER BY "Extent1".deptno DESC, "Extent1".loc DESC
LIMIT 1
Please upgrade to the 6.1.40 version. If this doesn't help, send us a small test project to reproduce the issue in our environment.

Post Reply