Customizing the Table Control: Sortable by Column (Kevin Mehlhaff)

Customizing the Table Control: Sortable by Column (Kevin Mehlhaff)

  • Comments 16

The Table control is a new way to display a collection of data in the LightSwitch HTML Client as of Visual Studio 2013. Like all controls in the LightSwitch HTML client, you can take over the rendering and write your own custom code. Today we will be leveraging the power of the LightSwitch middle tier and the flexibility of HTML to make one such customization. We will be adding sortable column headers to the table so that the user of the application can choose to sort by a particular field. This makes it easy for the user to find the data he or she is looking for.

Adding Custom Query

First we will create a new LightSwitch HTML application. For this example, attach to an external data source, choose the Northwind OData feed at http://services.odata.org/Northwind/Northwind.svc and import the Customer entity.

Then add a new server query on the Customers table, named SortedCustomers.

screen1

Next add two new parameters to the query, SortPropertyName, of type String, and SortAscending of type Boolean:

screen2

Then select Write Code to edit the SortedCustomers_PreprocessQuery method:

 screen4

In order to sort by a dynamic property name we will need to build up the LINQ query programmatically. Create two Extension methods on the IQueryable interface in order to do this. Add the following class before the NorthwindEntitiesDataService class definition:

VB:

Module OrderByExtensions

    Private ReadOnly OrderByMethod As MethodInfo = 
GetType(Queryable).GetMethods().
Where(
Function(method) method.Name = "OrderBy").Where(Function(method)
method.GetParameters().Length = 2).[Single]()
Private ReadOnly OrderByDescendingMethod As MethodInfo =
GetType(Queryable).GetMethods().
Where(
Function(method) method.Name = "OrderByDescending").
Where(
Function(method) method.GetParameters().Length = 2).[Single]() Private Function GetOrderByMethodForProperty(Of TSource)(source As IQueryable(Of TSource),
propertyName
As String,
orderByMethod
As MethodInfo)
As IQueryable(Of TSource) ' Create a parameter "x", where x is of TSource type Dim parameter As ParameterExpression = Expression.Parameter(GetType(TSource), "x") ' Access a property on the parameter: "x.<propertyName>" Dim parameterProperty As Expression = Expression.[Property](parameter, propertyName) ' Create a lambda of the form "x => x.<propertyName>" Dim lambda As LambdaExpression = Expression.Lambda(parameterProperty, {parameter}) Dim orderByMethodTyped As MethodInfo =
orderByMethod.MakeGenericMethod({
GetType(TSource), parameterProperty.Type}) Dim retVal = orderByMethodTyped.Invoke(Nothing, New Object() {source, lambda})
Return DirectCast(retVal, IQueryable(Of TSource)) End Function <System.Runtime.CompilerServices.Extension> Public Function OrderByPropertyName(Of TSource)(source As IQueryable(Of TSource),
propertyName
As String)
As IQueryable(Of TSource)
Return GetOrderByMethodForProperty(Of TSource)(source, propertyName, OrderByMethod) End Function <System.Runtime.CompilerServices.Extension> Public Function OrderByPropertyNameDescending(Of TSource)(source As IQueryable(Of TSource),
propertyName
As String)
As IQueryable(Of TSource) Return GetOrderByMethodForProperty(Of TSource)(source,
propertyName,
OrderByDescendingMethod)
End Function End Module

C#:

public static class OrderByExtensions
    {
        private static readonly MethodInfo OrderByMethod =
            typeof(Queryable).GetMethods()
            .Where(method => method.Name == "OrderBy")
            .Where(method => method.GetParameters().Length == 2)
            .Single();

        private static readonly MethodInfo OrderByDescendingMethod =
            typeof(Queryable).GetMethods()
            .Where(method => method.Name == "OrderByDescending")
            .Where(method => method.GetParameters().Length == 2)
            .Single();

        private static IQueryable<TSource> GetOrderByMethodForProperty<TSource>
            (IQueryable<TSource> source, string propertyName, MethodInfo orderByMethod)
        {
            // Create a parameter "x", where x is of TSource type
            ParameterExpression parameter = Expression.Parameter(typeof(TSource), "x");
            // Access a property on the parameter: "x.<propertyName>"
            Expression parameterProperty = Expression.Property(parameter, propertyName);
            // Create a lambda of the form "x => x.<propertyName>"
            LambdaExpression lambda = Expression.Lambda(parameterProperty, new[] { parameter });
            MethodInfo orderByMethodTyped = orderByMethod.MakeGenericMethod
                (new[] { typeof(TSource), parameterProperty.Type });
            object retVal = orderByMethodTyped.Invoke(null, new object[] { source, lambda });
            return (IQueryable<TSource>)retVal;
        }
        public static IQueryable<TSource> OrderByPropertyName<TSource>
            (this IQueryable<TSource> source, string propertyName)
        {
            return GetOrderByMethodForProperty<TSource>(source, propertyName, OrderByMethod);
        }

        public static IQueryable<TSource> OrderByPropertyNameDescending<TSource>
          (this IQueryable<TSource> source, string propertyName)
        {
            return GetOrderByMethodForProperty<TSource>(source, 
propertyName,
OrderByDescendingMethod); } }

We will also need to add two using statements at the top of the file.

VB:

Imports System.Reflection
Imports System.Linq.Expressions

C#:

using System.Reflection;
using System.Linq.Expressions;

Once we have these extension methods, we can use them in the SortedCustomers_PreprocessQuery method:

VB:

Private Sub SortedCustomers_PreprocessQuery(SortPropertyName As String,
        SortAscending As System.Nullable(Of Boolean),
        ByRef query As System.Linq.IQueryable(Of LightSwitchApplication.Customer))

    If Not String.IsNullOrEmpty(SortPropertyName) Then
        If (Not SortAscending.HasValue OrElse SortAscending.Value) Then
            query = query.OrderByPropertyName(SortPropertyName)
        Else
            query = query.OrderByPropertyNameDescending(SortPropertyName)
        End If
    End If
End Sub

C#:

 partial void SortedCustomers_PreprocessQuery(string SortPropertyName, 
bool? SortAscending, ref IQueryable<Customer> query) { if (!String.IsNullOrEmpty(SortPropertyName)) { if (!SortAscending.HasValue || SortAscending.Value) query = query.OrderByPropertyName(SortPropertyName); else query = query.OrderByPropertyNameDescending(SortPropertyName); } }

Adding Functionality to the Table Control

Now that we have a query that can handle sorting based on the name of a property, add a screen. Add a Browse Screen and select the SortedCustomers query as the data source:

screen5

Click OK to create the screen. Notice that in the Data Members List on the left two screen properties have been automatically added from the template. The first, CustomerSortPropertyName, is bound to the SortPropertyName parameter on the SortedCustomers query while the second, CustomerSortAscending, is bound to the SortAscending query parameter. These screen properties are automatically added to the screen in the Screen Content Tree. We will refer to these properties in custom code later but we do not need these on the screen, so select them from the tree and delete:

screen6

Now change the collection type of Sorted Customers on the screen from a List to a Table control:

screen5-5

Edit the postRender method for the Table by selecting the Table on the screen and clicking the Write Code dropdown:

screen7

Since everything in JavaScript is an object, give a name, CustomerPostRender, to the postRender function so we can reference it later. In this case we will use it to store static variables that retain their values between calls to the function. Add the following code to the function:

// Give a name, CustomerPostRender, to this function so 
// that we can use it to store static variables
myapp.BrowseSortedCustomers.Customer_postRender =
function CustomerPostRender(element, contentItem) { // Write code here. if (CustomerPostRender.ascending == undefined) { CustomerPostRender.ascending =
contentItem.screen.CustomerSortAscending != undefined ? contentItem.screen.CustomerSortAscending :
true; } $("th", $(element)).each(function (i) { // Get the column header contentItem based on the index var headerContentItem = contentItem.children[0].children[i]; // Some columns might contain contentItems that do not directly display the value // of a sortable property. For example, a column could contain a navigation property // (navigation properties are not order comparable) or a column could contain a // button. We skip adding an onclick handler in these cases. if (headerContentItem.kind !== "Value") { return; } var propertyName = headerContentItem.name; // Add a click handler for each table header $(this).on("click", function () { var text = $(this).text(); // The same column has been clicked twice, so reverse the sort order. if (CustomerPostRender.lastColumnClicked == this) { text = $(CustomerPostRender.lastColumnClicked).data("originalText"); CustomerPostRender.ascending = !CustomerPostRender.ascending; } else { // A different table header was clicked than the previous one if (CustomerPostRender.lastColumnClicked != undefined) { // Reset the last table header to remove the sort graphic $(CustomerPostRender.lastColumnClicked).html( $(CustomerPostRender.lastColumnClicked).data("originalText")); } } applySortGraphic(this, text, CustomerPostRender.ascending); contentItem.screen.CustomerSortPropertyName = propertyName; contentItem.screen.CustomerSortAscending = CustomerPostRender.ascending; // Store the original text of the table header by using the JQuery data api $(this).data("originalText", text); CustomerPostRender.lastColumnClicked = this; }); // Set the column that is sorted initially if (propertyName == contentItem.screen.CustomerSortPropertyName) { $(this).data("originalText", $(this).text()); CustomerPostRender.lastColumnClicked = this; applySortGraphic(this, $(this).text(), CustomerPostRender.ascending); } }); };

Add an additional function to the code-behind file to apply the sort graphic. Here we are just using HTML entities to make it easy to display an up triangle or a down triangle:

function applySortGraphic(element, text, ascending) {
    // Use html entity for up triangle and down triangle respectively
    var graphic = ascending ? "&#9650;" : "&#9660;";
    $(element).html(text + " " + graphic);
}

To sort by a default property and direction we can edit the screen’s created entry point to set default values. Go back to the screen designer and select the created entry in the Write Code drop down:

screen9

Add the following code to set the table to initially sort by the ContactName property in the ascending direction:

myapp.BrowseSortedCustomers.created = function (screen) {
    screen.CustomerSortAscending = true;
    screen.CustomerSortPropertyName = "ContactName";
};

Now if we F5, we see the browse screen that is initially sorted by the Customer’s ContactName property. Clicking a different column header will sort by that column while clicking the same column header twice will reverse the sort direction.

screen10

Conclusion

Using the flexibility of HTML and the LightSwitch middle tier, we were quickly able to add additional functionality on top of the existing Table control. All we had to do was create a custom server query that could sort on a property name and then create a basic UI on top of the column headers.

- Kevin Mehlhaff, Software Development Engineer in Test, LightSwitch Team

Leave a Comment
  • Please add 7 and 3 and type the answer here:
  • Post
  • you should be providing this for us out of the box

  • All you will do in HTML client has thousands of lines of code.

    I'm much more silverlight client that is a real LOB app.

    Very simple and straightforward

  • Guys the middle tier is showing you how to dynamically sort columns using LINQ. You could use it on your Silverlight clients as well. The middle-tier query is not specific to the HTML client at all.

    We hear you loud and clear that this should be built-in to the HTML table control. However, it isn't right now and this is a common request. So we showed you a robust way to do this. I suggest you create a UserVoice request so we can consider it for a future release. visualstudio.uservoice.com

    Thanks,

    -Beth

  • This is very helpful. I've been looking for a tutorial on this! Thanks!

  • The sorting capabilities have revolutionized the way I write LOB apps. Thank you LightSwtich team and Kevin Mehlhaff!

  • Thanks for this.  I agree that the ability to do dynamic sort and filter using property names should be built into the middle tier.  Here is a UV suggestion, please vote:

    visualstudio.uservoice.com/.../4458683-enable-dynamic-queries

  • This is well done and very helpful. This is not a lot of code.

  • Thank you for providing this Beth and Kevin - it is much appreciated.

    Another feature of the Table control that will be very useful is to have traditional paging where it shows only the current page of records with a set of Previous and Next page buttons. I know it already does continuous scrolling paging so traditional paging should not be that hard to add...

    The more I work with the HTML client the more impressive it gets!

  • I realize the html table does not have the sort-able grid, but this is nuts.

    I am using LS for a client app that has a very small budget, and the client loved the prototype I did using the browser based grid, the sort-able columns are a must, and I was asked to provide and html client version. I have searched high and low for simple technique to do this and this is what I find? Yikdes, there goes my budget for doing sort-able htl tables on several screens...

    I searched for js alternatives, and found several including ng-grid (anguar & jquery), Slickgrid (js, jquery, css), SpreadJS and it looks like all of them will be easier to implement using a server query, and doing this on the client instead of middle-tier.

    Normally, I am behind you guys on the tech and samples for LS and it is a great tool, but the non-sortable grid is just a real piece of...work.

    Oh yeah, I will go to voice now and leave my rant for getting a sort-able html grid I can be proud to use and not freak out over several hours of learning and coding to implement what took me 2 hours on four screens in the browser version.

    Getting off the soapbox, done pissin and moaning...

    Have a happy Thanksgiving Michael and Beth, you guys rock! Thanks for all your hard work!

  • Hey, I dont know where to ask this question, so i am taking a chance here. Please dont kill me :) . I am using lightswitch and i want to fire a piece of code that i have in the server project. Now my button is in the client, so how do i get the code to fire (create an xml file) when the user click a button?

  • Please check and if someone can resolve please help.. stackoverflow.com/.../client-and-server-projects-in-lightswitch

  • @mavs

    Official forum

    social.msdn.microsoft.com/.../home

  • This was a great post. I agree it should be part of the grid but the middle tier part was piece of cake I copied and pasted and added SortBy fields to my Lists (I do not use Tables ) within minutes! Now my screens have Sort By feature which looks cool!

  • Thank you... this does work well.  

    Can you help with this scenario?

    I need to sort by a property on a related table.  For example, I have two tables named Contact and ContactType.  I'm showing the ContactType.Name property in my grid containing Contacts.  How would you extend this solution to allow sorting by ContactType.Name?

  • @MJHSoftware you can write a specific case for the ContactType column in the PreprocessQuery method and add an on-click handler for this specific column in the JavaScript code. Otherwise, if you'd like to post this question in the forums, I can answer with some more advanced ideas on how to generalize this for any navigation property.

Page 1 of 2 (16 items) 12