Hi, I have encountered this problem when I load a collection FetchEntityCollection.

LLBLGenPro Version : 3 Runtime Library: SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll 3.0.10.0129 SD.LLBLGen.Pro.DQE.OracleODPNet.NET20.dll 3.0.10.0129

Database Oracle10g2 Database Driver ODT Oracle.DataAccess.dll 2.111.7.0

if i use this code

            SortExpression sort = new SortExpression(TableTestFields.Code | SortOperator.Ascending);
            adapter.FetchEntityCollection(entityCollection1, null, 0, sort, 1, 100);

this is trace

Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT "TESTLINQ"."TABLETEST"."CODE" AS "Code", "TESTLINQ"."TABLETEST"."DESCRIPTION" AS "Description", "TESTLINQ"."TABLETEST"."OID" AS "Oid" FROM "TESTLINQ"."TABLETEST" ORDER BY "TESTLINQ"."TABLETEST"."CODE" ASC
Method Exit: CreateSelectDQ
Generated Sql query: 
    Query: SELECT * FROM (SELECT a.*, rownum r___ FROM (SELECT "TESTLINQ"."TABLETEST"."CODE" AS "Code", "TESTLINQ"."TABLETEST"."DESCRIPTION" AS "Description", "TESTLINQ"."TABLETEST"."OID" AS "Oid" FROM "TESTLINQ"."TABLETEST" ORDER BY "TESTLINQ"."TABLETEST"."CODE" ASC) a WHERE rownum < :p4) WHERE r___ >=:p2
    Parameter: :p4 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 101.
    Parameter: :p2 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
Method Exit: CreatePagingSelectDQ

but when try

            SortExpression sort = new SortExpression(TableTestFields.Code | SortOperator.Ascending);
            adapter.FetchEntityCollection(entityCollection1, null, 100, sort);

trace is

Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT "TESTLINQ"."TABLETEST"."CODE" AS "Code", "TESTLINQ"."TABLETEST"."DESCRIPTION" AS "Description", "TESTLINQ"."TABLETEST"."OID" AS "Oid" FROM "TESTLINQ"."TABLETEST" WHERE rownum <= 100 ORDER BY "TESTLINQ"."TABLETEST"."CODE" ASC
Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.

correct query should be SELECT * FROM (SELECT "TESTLINQ"."TABLETEST"."CODE"AS "Code", "TESTLINQ"."TABLETEST"."DESCRIPTION" AS "Description", "TESTLINQ"."TABLETEST"."OID" AS "Oid" FROM "TESTLINQ"."TABLETEST" ORDER BY "TESTLINQ"."TABLETEST"."CODE" ASC)
WHERE rownum <= 100

same problem if i use linq command

IEnumerable<TableTestEntity> list = (from c in metaData.TableTest select c).Take(100).OrderBy(c => c.Code);

trace

: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[TestLinq.Db3.EntityClasses.TableTestEntity]).Select(c => c).Take(100).OrderBy(c => c.Code)
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT "LPA_L1"."CODE" AS "Code", "LPA_L1"."DESCRIPTION" AS "Description", "LPA_L1"."OID" AS "Oid" FROM "TESTLINQ"."TABLETEST" "LPA_L1" WHERE rownum <= 100 ORDER BY "LPA_L1"."CODE" ASC
Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.

Sorry for my english And thank for fantastic framework

To our knowledge the query gives the same results? It's a change which is also made in v2.6, as otherwise it's possible to run into this error: http://www.llblgen.com/tinyforum/GotoMessage.aspx?MessageID=95699&ThreadID=16159

Or is the resultset different because the WHERE clause is applied to the last table in the join list? (I ask this as it takes some time to get things setup like your query so it's easier to ask you )

Also, could you enable ansi joins? You switch them off by default apparently in the config file of your application, but you're using 10g, so you can use ansi joins (so you'll get INNER JOIN instead of ',' joins).

And thank for fantastic framework

Thanks!

The resultset is different, with yours query

   Query: SELECT "TESTLINQ"."TABLETEST"."CODE" AS "Code", "TESTLINQ"."TABLETEST"."DESCRIPTION" AS "Description", "TESTLINQ"."TABLETEST"."OID" AS "Oid" FROM "TESTLINQ"."TABLETEST" WHERE rownum <= 100 ORDER BY "TESTLINQ"."TABLETEST"."CODE" ASC

the result is sorted only for the row fetched, with my code, first sorting row and later return first 100 row (sorted)

daniele wrote:

The resultset is different, with yours query

   Query: SELECT "TESTLINQ"."TABLETEST"."CODE" AS "Code", "TESTLINQ"."TABLETEST"."DESCRIPTION" AS "Description", "TESTLINQ"."TABLETEST"."OID" AS "Oid" FROM "TESTLINQ"."TABLETEST" WHERE rownum <= 100 ORDER BY "TESTLINQ"."TABLETEST"."CODE" ASC

the result is sorted only for the row fetched, with my code, first sorting row and later return first 100 row (sorted)

aha! that's indeed a silly mistake we made . We'll get back to you a.s.a.p. with a fix. It can be monday before the fix is ready though. I hope that's soon enough for you.

The infrastructure was in place to make the proper decision (namely when an aggregate is in the query, we fall back to the old scenario, which was the wrapping query), it wasn't checking for group by and order by (group by queries have the same problem).

I've attached a new build for the oracle dqe for ODP.NET v3.0.

I tested the patch and everything works fine ... Thanks