none
HTML Table Server Side Sort

    General discussion

  • I gather from comments on this blog there is no support for server side sort in HTML table control.  This thread is to discuss a solution and hopefully incite a sample from LS team that we can use:

    Xander describes the use case in comments here:

    http://blogs.msdn.com/b/lightswitch/archive/2013/09/11/the-lightswitch-html-table-control-rohit-agrawal.aspx

    I tried using PreProcessQuery to accept a string parameter of field names for OrderBy and it worked like so:

    Imports System.Linq.Dynamic
    
    Namespace LightSwitchApplication
    
        Public Class DEVDataService
    
            Private Sub SortedContacts_PreprocessQuery(SortString As String, ByRef query As System.Linq.IQueryable(Of LightSwitchApplication.Contact))
                If Not 0 = Len(SortString) Then
                    'SortString = "it." & SortString
                    query = query.OrderBy(SortString)
                End If
    
            End Sub
        End Class
    
    End Namespace

    Had to enable OrderBy method to accept strings by using this old Dynamic Linq Library:
    http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

    I gather the library was never released by MS, but it works in LS like so:

    1) Download the sample (VB or C#) from links on above blog
    2) Add dynamic.vb(.cs) to UserCode dir in Server project
    3) Add Imports(using) statement to reference System.Linq.Dynamic in DataService.vb(.cs)
    4) Create Query with optional parameter 'SortString' of type String
    5)  Use the following in PreProcessQuery:  query = query.OrderBy(SortString)

    I think the dynamic.vb code even handles as tSQL-like comma delim string of field names and descending sorts like the following which is impressive.

    query = query.OrderBy("LastName, FirstName DESC")

    Results:

    It would be really nice if this dynamic Linq functionality was built into LS so we could use strings instead of lambda in OrderBy & Where clauses.

    Beyond that it's just a matter of applying events to the table header as Xander said. 

    Beth, do you think we could get a blog or sample that'll bring this approach to completion for HTML Table control?

    Your comments are welcomed.

    HTH,
    Josh





    • Edited by joshbooker Friday, September 20, 2013 5:15 PM
    Friday, September 20, 2013 4:54 PM

All replies

  • Vote to Enable Dynamic Queries here:

    User Voice: enable-dynamic-queries

    Friday, September 20, 2013 6:14 PM
  • Perhaps I'm not understanding what it unique about this sorting strategy demonstrated here, but I was able to accomplish (I think) the exact same thing using the standard PreprocessQuery without any additional libraries, sorting across table fields Last Name and First Name and excluding duplicates as follows:

            partial void InactivePatientSearch_PreprocessQuery(string SearchString, ref IQueryable<HospitalData> query)
            {
                query =
                    query.GroupBy(p => new { p.Patient.LastName, p.Patient.FirstName })
                        .Select(p => p.FirstOrDefault())
                        .OrderBy(p => p.Patient.LastName)
                        .ThenBy(p => p.Patient.FirstName);
            }
    

    Thursday, September 26, 2013 11:52 PM
  • Thanks Allen.

    What's unique is that the sort string is a parameter thus can be controlled by the user at runtime.

    What if your user wants to sort patients by FirstName?

    The Linq Dynamic Query Library extends the OrderBy and Where methods to accept strings instead of accepting only lambda expressions.

    The Where extension is nice too...For example, assuming your variable 'SearchString' is an optional parameter in your query with a value of "LastName='Adams' and State='NY'" set by the client app, using the library, you could do something like: 

            partial void InactivePatientSearch_PreprocessQuery(string SearchString, ref IQueryable<HospitalData> query)
            {
                query =
                    query.GroupBy(p => new { p.Patient.LastName, p.Patient.FirstName })
                        .Where(SearchString)
                        .Select(p => p.FirstOrDefault())
                        .OrderBy(p => p.Patient.LastName)
                        .ThenBy(p => p.Patient.FirstName);
            }


    • Edited by joshbooker Friday, September 27, 2013 1:05 AM
    Friday, September 27, 2013 12:22 AM
  • Here is an example of how convoluted it can get to try to enable dynamic sort.

    http://social.msdn.microsoft.com/Forums/vstudio/en-US/1cef2b81-90d0-4c9a-856f-410c7591dfbc/preprocess-query-adding-sort-order-help

    The dynamic query library solves this while also handling multiple sort fields as well as descending sorts.

    • Edited by joshbooker Friday, September 27, 2013 12:38 AM fix link
    Friday, September 27, 2013 12:35 AM
  • The LS team posted a sample of how to sort table control using property names here: http://blogs.msdn.com/b/lightswitch/archive/2013/11/26/customizing-the-table-control-sortable-by-column-kevin-mehlhaff.aspx While it works, I'd love to see the Linq Dynamic Query Library built in to the middle tier because instead of adding new methods, the dynamic library extends the OrderBy and Where methods making it so you can pass either lambda or property name strings. Also, the library extends thenBy method so it can handle multiple sort properties.
    • Edited by joshbooker Friday, November 29, 2013 1:31 PM
    Tuesday, November 26, 2013 8:20 PM