Home > Oracle Database, SQL Server, SQLAzure > Implementing Database Dynamic Sorting using CodeFluent Entities

Implementing Database Dynamic Sorting using CodeFluent Entities


As explained in a previous post, using the ORDER BY statement in your CFQL methods you can sort collection of records when loading them from database. However, since the query is created at design time, at run time results will always be sorted on the same column and in the defined direction.

Using CodeFluent Entities you can also set-up dynamic sorting implemented in the persistence layer to be as efficient as possible on large amount of data. Dynamic sorting is disabled by default but can be enabled easily:

  • At project level (i.e. for all entities) by setting the “Default Sortable” property to “True
  • At entity level (i.e. can sort on all columns) by setting the “Default Sortable” property to “True
  • At property level (i.e. enable dynamic sorting for this specific column) by setting the “Is Sortable” property to “True

What happens then is that upon the next generation pairs of sorting parameters (_orderBy and _orderByDirection) are added to the corresponding stored procedures and depending on their containing values, the loaded records will by sorted by those.

For instance here’s a sample LoadAll stored procedure of an entity with two properties (Date and Code) that were defined sortable:

CREATE PROCEDURE [dbo].[Order_LoadAll]
(
@_orderBy0 [nvarchar] (64) = NULL,
@_orderByDirection0 [BIT] = 0
)
AS
SET NOCOUNT ON
SELECT [Order].[Order_Id], [Order].[Order_Code], [Order].[Order_Date], [Order].[Order_Customer_Id], [Order].[_trackLastWriteTime], [Order].[_trackCreationTime], [Order].[_trackLastWriteUser], [Order].[_trackCreationUser], [Order].[_rowVersion]
FROM [Order]
ORDER BY CASE
WHEN @_orderBy0 = ‘[Order].[Date]’ AND @_orderByDirection0 = 0 THEN [Order].[Order_Date]
END ASC,
CASE
WHEN @_orderBy0 = ‘[Order].[Date]’ AND @_orderByDirection0 = 1 THEN [Order].[Order_Date]
END DESC,
CASE
WHEN @_orderBy0 = ‘[Order].[Code]’ AND @_orderByDirection0 = 0 THEN [Order].[Order_Code]
END ASC,
CASE
WHEN @_orderBy0 = ‘[Order].[Code]’ AND @_orderByDirection0 = 1 THEN [Order].[Order_Code]
END DESC
RETURN

As you can see, depending on the Sort Expression contained by the _orderBy parameter and the direction indicated by the _orderByDirection parameter, the returned data will be ordered, at run time, by a column or another, in a direction or another.

Note that if you need more than one pair of order parameters (orderBy & orderByDirection) you can control their number using the “Order By Parameter Count” properties:

  • At project level: “Default Persistence Order By Parameter Count
  • At entity level: “Persistence Order By Parameter Count
  • At method level: “Persistence Order By Parameter Count
  1. Peter Stanford
    April 24, 2013 at 2:17 am

    I’m finding this difficult to implement. I have a simple entity that I use to populate dropdown lists, i.e. a reference entity. I’ve set the Default Sortable property for the entity to True, I’ve set the Is Sortable property for the appropriate property to True and have regenerated the model. The resulting stored procedure for the LoadAll method now shows the additional Order By code but the entity values are NOT sorted when I populate the dropdown list. Do I have to add something, i.e. create my own LoadAll method or is there some other mechanism to make this work?

    • SoftFluent
      April 24, 2013 at 4:27 pm

      Hi,
      The “Default Sortable” attribute (at the Project and Entity scope) and the “Is Sortable” attribute (at the Property scope) indicate that you CAN order the results by a property name, those attributes do NOT perform any default order.

      The “LoadAll” method calls the “PageLoadAll” method internally.

      If you want to order your results you need to use the “PageLoadAll(int pageIndex, int pageSize, CodeFluent.Runtime.PageOptions pageOptions)” method using the wanted sorting options for the “pageOptions” object.

      You can take a look at the video on this post : https://blog.codefluententities.com/2011/01/24/sorting-and-paging-in-an-asp-net-web-site/

      • Peter Stanford
        April 25, 2013 at 9:03 am

        OK, thanks, I’ve worked it out. I needed to add a reference to the CodeFluent.Runtime dll

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s