LINQ query pagination - SQL generation RRS feed

  • Question

  • Using de following LINQ query and EF6, using ODP.NET as provider:

    db.SAMPLES.OrderBy(p => p.NAME).ThenBy(p => p.DESCRIPTION).Skip(0).Take(10).ToList();

    I got the following SQL:

    SELECT *

    FROM (


    "Extent1"."ID" AS "ID",

    "Extent1"."NUMBER" AS "NUMBER",

    "Extent1"."NAME" AS "NAME",


    FROM ( SELECT "Extent1"."ID" AS "ID", "Extent1"."NUMBER" AS "NUMBER", "Extent1"."NAME" AS "NAME", "Extent1"."DESCRIPTION" AS "DESCRIPTION", row_number() OVER (ORDER BY "Extent1"."NAME" ASC, "Extent1"."DESCRIPTION" ASC) AS "row_number"

      FROM "ZENKI"."SAMPLEs" "Extent1"

    )  "Extent1"

    WHERE ("Extent1"."row_number" > 0)

    ORDER BY "Extent1"."NAME" ASC, "Extent1"."DESCRIPTION" ASC


    WHERE (ROWNUM <= (10) )

    I'm wondering why the last order by (in bold). seems to ne unnecessary and is killing query performance. I asked in the forum and got the following response:

    Providers, such as ODP.NET, have limited control over the SQL generated. Ask Microsft.

    Saturday, March 3, 2018 9:42 AM

All replies

  • I think that the ordering is required since the portion that is filtered by ‘WHERE ("Extent1"."row_number" > …)’ is not necessarily returned as a sorted sequence. Perhaps, both of ORDER BY are interpreted in an optimised manner by the server.

    You can make some experiments and find a better SQL query, and execute it directly, if it is allowed.

    By the way, in case of modern SQL Server the query is different:

       SELECT …

       FROM [Samples] AS [Extent1]

       ORDER BY [Extent1].[Name] ASC, [Extent1].[Description] ASC


    Sunday, March 4, 2018 8:41 AM
  • The query runs in Oracle. I don't know how it behaves in SQL Server, but in Oracle the WHERE ("Extent1"."row_number" > …)’  is already sorted.
    Sunday, March 4, 2018 2:12 PM
  • Hi danijepg,

    >>The query runs in Oracle. I don't know how it behaves in SQL Server, but in Oracle the ‘WHERE ("Extent1"."row_number" > …)’  is already sorted.

    As Viorel_ said, SQL Server generate a different SQL Statement, because different databases have different special function, so different provide will generate different SQL Statement, I think it is a ODP.Net issue. if want to avoid the performance issue, as a workaround, you could use entity framework Raw SQL. For more information, please refer to:

    In addition, entity framework have been open resourced. you could also refer to related code resource.

    Best regards,


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact

    Monday, March 5, 2018 3:09 AM