How does one do an Oracle top-n (paged) query in SubSonic 2.2?

Question

(Disclaimer: I changed/obfuscated some of the variable/table/column names here for security reasons. Please forgive me if something looks a little off.)

I am building a front-end to an Oracle 10g database, and I'm trying to get paged data. Aside from paging, the following SubSonic 2.2 code gives me what I want, in the order I want it:

var q = new Select()
  .From(AppDb.MyTable.Schema)
  .Where(AppDb.MyTable.DisabledDateColumn).IsNull()
  .OrderDesc(AppDb.MyTable.CreatedDateColumn.ColumnName)

System.Console.Out.Writeline(q.BuildStatement());

This yields the following SQL:

SELECT
    MYSCHEMA.MYTABLE.ID,
    MYSCHEMA.MYTABLE.DISABLED_DATE
FROM
    MYSCHEMA.MYTABLE
WHERE
    MYSCHEMA.MYTABLE.DISABLED_DATE IS NULL
ORDER BY
    CREATED_DATE DESC

Then I try to introduce paging:

var q = new Select()
  .From(AppDb.MyTable.Schema)
  .Where(AppDb.MyTable.DisabledDateColumn).IsNull()
  .OrderDesc(AppDb.MyTable.CreatedDateColumn.ColumnName)
  .Paged(0, 10);

And it wipes out my WHERE and ORDER BY clauses:

SELECT * FROM (
    SELECT
        MYSCHEMA.MYTABLE.ID,
        MYSCHEMA.MYTABLE.DISABLED_DATE,
        ROWNUM as row_number
    FROM
        MYSCHEMA.MYTABLE
)
WHERE
    row_number BETWEEN 1 AND 10

I'm brand new to SubSonic, so I don't know if that's a bug, or I'm just not doing it the preferred way, or if Oracle demands that this be done in a different, more Oracle-centric way. (Oracle seems to demand that of everything.)

What I want, in case it isn't obvious, is each succeeding page to include the 10 next most-recently-created, non-disabled records. How can I do this in SubSonic 2.2?

Answer 1

Oracle would be fine with the Top-N query if the inner SQL would consist of the first statement (including where and order by).

So, i'd say there is no Oracle specific reason to omit them.

Never used subsonic, don't know if you need to do it different there.

Performance wise, an index on DISABLED_DATE, CREATED_DATE should do the trick (see: http://blog.fatalmind.com/2010/07/30/analytic-top-n-queries/).

Answer 2

It looks like your query is not generating the same SQL as the current Oracle Data Provider for SubSonic should. Here is the relevant code (starting at line 852):

    if(qry.PageIndex < 0)
        query = String.Format("{0} {1} FROM {2}.{3} {4} {5}",select ,columns, table.SchemaName, table.Name, where, order);
    else
    {
        int start = qry.PageIndex * qry.PageSize;
        int end = (qry.PageIndex + 1) * qry.PageSize;

        const string cteFormat =
                    "WITH pagedtable AS (SELECT {0}, ROW_NUMBER () OVER ({1}) AS rowindex FROM {2}.{3} {4}) SELECT {5}, rowindex FROM pagedtable WHERE rowindex >= {6} AND rowindex < {7} ORDER BY rowindex";
        query = string.Format(cteFormat, columns, order,table.SchemaName, table.Name, where, columns.Replace(table.Name + ".", String.Empty), start, end);
    }
    return query;

Perhaps an update to current source would do the trick. If there is something wrong with the actual provider, you can compare it to the SqlDataProvider for hints as to what might be the problem.