none
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 (

    SELECT

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

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

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

    "Extent1"."DESCRIPTION" AS "DESCRIPTION"

    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 odp.net forum and got the following response:

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

    https://community.oracle.com/thread/4126265

    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

       OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

    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:

    https://msdn.microsoft.com/en-us/library/jj592907%28v=vs.113%29.aspx?f=255&MSPPError=-2147217396

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

    https://github.com/aspnet/EntityFramework6

    Best regards,

    Zhanglong


    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 MSDNFSF@microsoft.com.

    Monday, March 5, 2018 3:09 AM
    Moderator