none
Subquery with take and skip RRS feed

  • Question

  • Hi All

    I would like to run a subquery with LINQ and Oracle.

    My query is using Skip and Take methods for pagination purposes.

    I first want to obtain just IDs from my table after being paginated and then make a Join over these IDs to fetch other fields.

    Below is my query:

    var query = (from item in dbContext.CATEGORIAS
                       orderby item.ID descending
                       select new
                       {
                           id = item.ID
                       }).Skip(0).Take(10);
                       
                       
    var query2 = from item in query
                    from item2 in dbContext.CATEGORIAS
                    where item.id == item2.ID
                    select new { item2.DESCRIPCION_LARGA };
                        
                       
    query2.ToList();

    Please note I have simplified above for illustration.

    The generated SQL statement is as follows:


    SELECT
    1 AS "C1",
    "Extent2"."DESCRIPCION_LARGA" AS "DESCRIPCION_LARGA"
    FROM   (SELECT *
            FROM (
            SELECT "Extent1"."ID" AS "ID", "Extent1"."DESCRIPCION_LARGA" AS "DESCRIPCION_LARGA", "Extent1"."DESCRIPCION_MED" AS "DESCRIPCION_MED", "Extent1"."DESCRIPCION_CORTA" AS "DESCRIPCION_CORTA", "Extent1"."ABREVIACION" AS "ABREVIACION", "Extent1"."PADRE_ID" AS "PADRE_ID", "Extent1"."ESTADO_ID" AS "ESTADO_ID"
            FROM ( SELECT "Extent1"."ID" AS "ID", "Extent1"."DESCRIPCION_LARGA" AS "DESCRIPCION_LARGA", "Extent1"."DESCRIPCION_MED" AS "DESCRIPCION_MED", "Extent1"."DESCRIPCION_CORTA" AS "DESCRIPCION_CORTA", "Extent1"."ABREVIACION" AS "ABREVIACION", "Extent1"."PADRE_ID" AS "PADRE_ID", "Extent1"."ESTADO_ID" AS "ESTADO_ID", row_number() OVER (ORDER BY "Extent1"."ID" DESC) AS "row_number"
                    FROM "CATEGORIAS" "Extent1"
            )  "Extent1"
            WHERE ("Extent1"."row_number" > 0)
            ORDER BY "Extent1"."ID" DESC
            )
            WHERE (ROWNUM <= (10) ) ) "Limit1"
    INNER JOIN "CATEGORIAS" "Extent2" ON "Limit1"."ID" = "Extent2"."ID"


    As you can see here it selects many columns which are not needed.


    What I expect is the following:

    SELECT
    1 AS "C1",
    "Extent2"."DESCRIPCION_LARGA" AS "DESCRIPCION_LARGA"
    FROM   (SELECT *
            FROM (
            SELECT "Extent1"."ID" AS "ID""
            FROM ( SELECT "Extent1"."ID" AS "ID", row_number() OVER (ORDER BY "Extent1"."ID" DESC) AS "row_number"
                    FROM "CATEGORIAS" "Extent1"
            )  "Extent1"
            WHERE ("Extent1"."row_number" > 0)
            ORDER BY "Extent1"."ID" DESC
            )
            WHERE (ROWNUM <= (10) ) ) "Limit1"
    INNER JOIN "CATEGORIAS" "Extent2" ON "Limit1"."ID" = "Extent2"."ID"

    Is there any what I can make it runs the statemente I expect?

    Thanks in advance and best regards,
    Fabian
    Saturday, October 8, 2016 6:05 PM

Answers

  • Dear Cole,

    Thank you very much for your help.

    Just curious, might it be the case that the Entity Framework for Oracle behaves differently as for SQL Server?

    I am asking because I keep getting the same unexpected results (too may columns in the SELECT).  I found a workaround by doing the following:

    var dummy1 = 1;
    
    var query = (from item in dbContext.CATEGORIAS.Where(x => 1 == dummy1)
    		orderby item.ID descending
    		select new
    		{
    			id = item.ID
    		}).Skip(0).Take(10);
    
    var query2 = 	from item in query
    		from item2 in dbContext.CATEGORIAS
    		where item.id == item2.ID
    		select new { item2.DESCRIPCION_LARGA };

    When I passed a referenced parameter then it is ok.

    Best regards,

    Fabian

    Wednesday, October 12, 2016 4:12 AM

All replies

  • Hi Fabian von Romberg,

    According to your description, you are using Oracle database, From our policy, we couldn't install Oracle Database, But I create a demo by using SQL server 2014, it seems OK.

    #LINQ Statement:

     var query = (from item in dbContext.CATEGORIAS
                                 orderby item.ID descending
                                 select new
                                 {
                                     id = item.ID
                                 }).Skip(0).Take(10);
    
                    var query2 = from item in query
                                 from item2 in dbContext.CATEGORIAS
                                 where item.id == item2.ID
                                 select new { item2.DESCRIPCION_LARGA };
    
                    Console.WriteLine(query2.ToString());

    #Generated SQL statement.

    SELECT
        [Limit1].[ID] AS [ID],
        [Extent2].[DESCRIPCION_LARGA] AS [DESCRIPCION_LARGA]
        FROM   (SELECT [Extent1].[ID] AS [ID]
            FROM [dbo].[CATEGORIAS] AS [Extent1]
            ORDER BY [Extent1].[ID] DESC
            OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY  ) AS [Limit1]
        INNER JOIN [dbo].[CATEGORIAS] AS [Extent2] ON [Limit1].[ID] = [Extent2].[ID]

    If you want to use SQL statement by manually, I would suggest that you could use RAW SQL with EF. For more information, please refer to:

    https://msdn.microsoft.com/en-us/data/jj592907.aspx

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, October 10, 2016 2:08 AM
    Moderator
  • Dear Cole,

    Thank you very much for your help.

    Just curious, might it be the case that the Entity Framework for Oracle behaves differently as for SQL Server?

    I am asking because I keep getting the same unexpected results (too may columns in the SELECT).  I found a workaround by doing the following:

    var dummy1 = 1;
    
    var query = (from item in dbContext.CATEGORIAS.Where(x => 1 == dummy1)
    		orderby item.ID descending
    		select new
    		{
    			id = item.ID
    		}).Skip(0).Take(10);
    
    var query2 = 	from item in query
    		from item2 in dbContext.CATEGORIAS
    		where item.id == item2.ID
    		select new { item2.DESCRIPCION_LARGA };

    When I passed a referenced parameter then it is ok.

    Best regards,

    Fabian

    Wednesday, October 12, 2016 4:12 AM
  • Hi Fabian von Romberg,

    I am glad to know you solved this problem and thanks for sharing the solution. 

    It will be very beneficial for other community members who have the similar questions. 

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, October 17, 2016 8:22 AM
    Moderator