none
EF4 + Pagination + Join + OrderBy RRS feed

  • Question

  • Hi,

    In our MVC application, we have an Index page where data is displayed using pagination. In some pages, we need to join some related tables in order to show foreign keys field descriptions. In addition, all these data can be sorted.

    When we have an Index page with these conditions, EF generate a query adding more than one condition to the Order By expression. Something like that:

    exec sp_executesql N'SELECT 
    [Project2].[TD_CI] AS [TD_CI], 
    [Project2].[TD_NOM] AS [TD_NOM], 
    [Project2].[TD_MOD] AS [TD_MOD], 
    [Project2].[TD_OBL] AS [TD_OBL], 
    [Project2].[TD_CAD] AS [TD_CAD], 
    [Project2].[TD_DES] AS [TD_DES], 
    [Project2].[TD_CT] AS [TD_CT], 
    [Project2].[EO_CI] AS [EO_CI], 
    [Project2].[ELE_EO_CI] AS [ELE_EO_CI], 
    [Project2].[EO_ID] AS [EO_ID], 
    [Project2].[EO_NOM] AS [EO_NOM], 
    [Project2].[EO_RAI_CI] AS [EO_RAI_CI], 
    [Project2].[EO_PATH] AS [EO_PATH], 
    [Project2].[C1] AS [C1], 
    [Project2].[TD_CI1] AS [TD_CI1], 
    [Project2].[TE_CI] AS [TE_CI], 
    [Project2].[TD_NOM1] AS [TD_NOM1], 
    [Project2].[TD_CT1] AS [TD_CT1], 
    [Project2].[TE_CI1] AS [TE_CI1], 
    [Project2].[TE_NOM] AS [TE_NOM], 
    [Project2].[TE_DES] AS [TE_DES]
    FROM ( SELECT 
    	[Limit1].[TD_CI] AS [TD_CI], 
    	[Limit1].[TD_NOM] AS [TD_NOM], 
    	[Limit1].[TD_MOD] AS [TD_MOD], 
    	[Limit1].[TD_OBL] AS [TD_OBL], 
    	[Limit1].[TD_CAD] AS [TD_CAD], 
    	[Limit1].[TD_DES] AS [TD_DES], 
    	[Limit1].[TD_CT] AS [TD_CT], 
    	[Limit1].[EO_CI] AS [EO_CI], 
    	[Limit1].[ELE_EO_CI] AS [ELE_EO_CI], 
    	[Limit1].[EO_ID] AS [EO_ID], 
    	[Limit1].[EO_NOM] AS [EO_NOM], 
    	[Limit1].[EO_RAI_CI] AS [EO_RAI_CI], 
    	[Limit1].[EO_PATH] AS [EO_PATH], 
    	[Join2].[TD_CI] AS [TD_CI1], 
    	[Join2].[TE_CI1] AS [TE_CI], 
    	[Join2].[TD_NOM] AS [TD_NOM1], 
    	[Join2].[TD_CT] AS [TD_CT1], 
    	[Join2].[TE_CI2] AS [TE_CI1], 
    	[Join2].[TE_NOM] AS [TE_NOM], 
    	[Join2].[TE_DES] AS [TE_DES], 
    	CASE WHEN ([Join2].[TD_CI] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
    	FROM   (SELECT TOP (40) [Project1].[TD_CI] AS [TD_CI], [Project1].[TD_NOM] AS [TD_NOM], [Project1].[TD_MOD] AS [TD_MOD], [Project1].[TD_OBL] AS [TD_OBL], [Project1].[TD_CAD] AS [TD_CAD], [Project1].[TD_DES] AS [TD_DES], [Project1].[TD_CT] AS [TD_CT], [Project1].[EO_CI] AS [EO_CI], [Project1].[ELE_EO_CI] AS [ELE_EO_CI], [Project1].[EO_ID] AS [EO_ID], [Project1].[EO_NOM] AS [EO_NOM], [Project1].[EO_RAI_CI] AS [EO_RAI_CI], [Project1].[EO_PATH] AS [EO_PATH]
    		FROM ( SELECT [Project1].[TD_CI] AS [TD_CI], [Project1].[TD_NOM] AS [TD_NOM], [Project1].[TD_MOD] AS [TD_MOD], [Project1].[TD_OBL] AS [TD_OBL], [Project1].[TD_CAD] AS [TD_CAD], [Project1].[TD_DES] AS [TD_DES], [Project1].[TD_CT] AS [TD_CT], [Project1].[EO_CI] AS [EO_CI], [Project1].[ELE_EO_CI] AS [ELE_EO_CI], [Project1].[EO_ID] AS [EO_ID], [Project1].[EO_NOM] AS [EO_NOM], [Project1].[EO_RAI_CI] AS [EO_RAI_CI], [Project1].[EO_PATH] AS [EO_PATH], row_number() OVER (ORDER BY [Project1].[TD_NOM] ASC) AS [row_number]
    			FROM ( SELECT 
    				[Extent1].[TD_CI] AS [TD_CI], 
    				[Extent1].[TD_NOM] AS [TD_NOM], 
    				[Extent1].[TD_MOD] AS [TD_MOD], 
    				[Extent1].[TD_OBL] AS [TD_OBL], 
    				[Extent1].[TD_CAD] AS [TD_CAD], 
    				[Extent1].[TD_DES] AS [TD_DES], 
    				[Extent1].[TD_CT] AS [TD_CT], 
    				[Extent2].[EO_CI] AS [EO_CI], 
    				[Extent2].[ELE_EO_CI] AS [ELE_EO_CI], 
    				[Extent2].[EO_ID] AS [EO_ID], 
    				[Extent2].[EO_NOM] AS [EO_NOM], 
    				[Extent2].[EO_RAI_CI] AS [EO_RAI_CI], 
    				[Extent2].[EO_PATH] AS [EO_PATH]
    				FROM  [dbo].[TIP_DOC] AS [Extent1]
    				LEFT OUTER JOIN [dbo].[ELE_ORG] AS [Extent2] ON [Extent1].[TD_CT] = [Extent2].[EO_CI]
    				WHERE @p__linq__0 = 1
    			)  AS [Project1]
    		)  AS [Project1]
    		WHERE [Project1].[row_number] > 0
    		ORDER BY [Project1].[TD_NOM] ASC ) AS [Limit1]
    	LEFT OUTER JOIN  (SELECT [Extent3].[TD_CI] AS [TD_CI], [Extent3].[TE_CI] AS [TE_CI1], [Extent3].[TD_NOM] AS [TD_NOM], [Extent3].[TD_CT] AS [TD_CT], [Extent4].[TE_CI] AS [TE_CI2], [Extent4].[TE_NOM] AS [TE_NOM], [Extent4].[TE_DES] AS [TE_DES]
    		FROM  [dbo].[TIP_DOC_TIP_EXT] AS [Extent3]
    		INNER JOIN [dbo].[TIP_EXT] AS [Extent4] ON [Extent3].[TE_CI] = [Extent4].[TE_CI] ) AS [Join2] ON [Limit1].[TD_CI] = [Join2].[TD_CI]
    )  AS [Project2]
    ORDER BY [Project2].[TD_NOM] ASC, [Project2].[TD_CI] ASC, [Project2].[EO_CI] ASC, [Project2].[C1] ASC',N'@p__linq__0 bit',@p__linq__0=1

    In this way, when we edit a detail and perform a navigation over records, it is possible that the order will change to respect this other navigation query (with only one Order By field):

    exec sp_executesql N'SELECT 
    [Project1].[TD_CI] AS [TD_CI]
    FROM ( SELECT 
    	[Extent1].[TD_CI] AS [TD_CI], 
    	[Extent1].[TD_NOM] AS [TD_NOM]
    	FROM [dbo].[TIP_DOC] AS [Extent1]
    	WHERE @p__linq__0 = 1
    )  AS [Project1]
    ORDER BY [Project1].[TD_NOM] ASC',N'@p__linq__0 bit',@p__linq__0=1

    Does anyone know how to avoid this problem in the Order By pagination query?

    Thanks in advance,

    JA Reyes.


    Please remember to Vote & "Mark As Answer" if this post is helpful to you.
    Por favor, recuerda Votar y "Marcar como respuesta" si la solución de esta pregunta te ha sido útil.ion

    • Edited by JA Reyes Monday, March 12, 2012 3:46 PM
    Monday, March 12, 2012 3:40 PM

Answers

  • Well,

    For those interested in this issue, it looks like the right solution is to perform always a ThenBy operation over the Primary Key Field after any Order By call.

    As the PK is unique, there is no problem related with random data returned when the field being sorted has duplicated values.

    Regards,

    JA Reyes.


    Please remember to Vote & "Mark As Answer" if this post is helpful to you.
    Por favor, recuerda Votar y "Marcar como respuesta" si la solución de esta pregunta te ha sido útil.

    • Marked as answer by JA Reyes Friday, March 16, 2012 9:00 AM
    Friday, March 16, 2012 9:00 AM

All replies

  • Hi JA Reyes,

    Welcome!

    I'm not sure whether you're using WCF Data Service in your project? if yes,please refer here: http://social.msdn.microsoft.com/Forums/pl-PL/adodotnetentityframework/thread/dc3ced01-3928-4b46-9954-b98dcf324075 

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, March 13, 2012 8:51 AM
    Moderator
  • Hi Alan,

    No, I'm not working with WCF Data Service. In my case, I have an n-layered architecture relaying on a Generic Repository pattern in my Infrastructure layer.

    The problem is basically the differences between how EF4 build the Order By expression performing a pagination query or not. That problem could arise in a different way about how we are displaying data to users on our Index page or when they perform a navigation over records being edited.

    In fact, the pagination inner query sorts records as expected:

    ORDER BY [Project1].[TD_NOM] ASC ) AS [Limit1]
    

    but later, it adds some other fields:

    ORDER BY [Project2].[TD_NOM] ASC, [Project2].[TD_CI] ASC, [Project2].[EO_CI] ASC, [Project2].[C1] ASC
    which depending on TD_CI and EO_CI values, this query could return data in a different order than in my second navigation query.

    We would greatly appreciate any suggestions about how to solve this problem.

    Regards,

    JA Reyes.


    Please remember to Vote & "Mark As Answer" if this post is helpful to you.
    Por favor, recuerda Votar y "Marcar como respuesta" si la solución de esta pregunta te ha sido útil.

    Tuesday, March 13, 2012 9:22 AM
  • Well,

    For those interested in this issue, it looks like the right solution is to perform always a ThenBy operation over the Primary Key Field after any Order By call.

    As the PK is unique, there is no problem related with random data returned when the field being sorted has duplicated values.

    Regards,

    JA Reyes.


    Please remember to Vote & "Mark As Answer" if this post is helpful to you.
    Por favor, recuerda Votar y "Marcar como respuesta" si la solución de esta pregunta te ha sido útil.

    • Marked as answer by JA Reyes Friday, March 16, 2012 9:00 AM
    Friday, March 16, 2012 9:00 AM
  • Hi JA Reyes,

    Thanks for sharing your experience here.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, March 16, 2012 9:13 AM
    Moderator