locked
Unable to sort on relations???? RRS feed

  • Question

  • Is it just me or does column sorting on entity relationships not working? Take a basic example, company with departments with employees. I have a data grid with department & employee name. I can sort on the employee fields but nothing happens when I click on the department column....

    Any ideas? Is this a know bug? I would expect one to be able to sort on related entities, not so?

    Friday, July 29, 2011 7:18 AM

Answers

  • Frank,

    Yes - sorting by the related parent field by clicking on the grid header does not appear to work.

    If you needed to sort by department, you could modify the underlying query so that grid is initially sorted by department. It's not ideal because the user would not be able to re-sort by department after changing the sort order to 'Employee Name' but it might be a workable work-around.

    Here's the code that you would use in the PreprocessQuery method to order by department. You could parameterise this query and extend it to build your own mechanism for sorting by department.

     

    Private Sub EmployeeSortByDept_PreprocessQuery(ByRef query As System.Linq.IQueryable(Of LightSwitchApplication.Employees))
       query = query.OrderBy(Function(a) a.Department.DepartmentName)
    End Sub
    
    

    Tim

     

    Friday, July 29, 2011 10:48 AM

All replies

  • Frank,

    Yes - sorting by the related parent field by clicking on the grid header does not appear to work.

    If you needed to sort by department, you could modify the underlying query so that grid is initially sorted by department. It's not ideal because the user would not be able to re-sort by department after changing the sort order to 'Employee Name' but it might be a workable work-around.

    Here's the code that you would use in the PreprocessQuery method to order by department. You could parameterise this query and extend it to build your own mechanism for sorting by department.

     

    Private Sub EmployeeSortByDept_PreprocessQuery(ByRef query As System.Linq.IQueryable(Of LightSwitchApplication.Employees))
       query = query.OrderBy(Function(a) a.Department.DepartmentName)
    End Sub
    
    

    Tim

     

    Friday, July 29, 2011 10:48 AM
  • Thank you! This did what I needed. What would be the syntax to sort by Name and Date? I don't know Linq yet...

    Thanks!

    Alex

    Saturday, August 6, 2011 12:32 AM
  • Just replace "DepartmentName" with "Name", or "Date" (assuming of course that "Department" has a property with those names, of course, so:

    Private Sub EmployeeSortByDept_PreprocessQuery(ByRef query As System.Linq.IQueryable(Of LightSwitchApplication.Employees))
      query = query.OrderBy(Function(a) a.Department.Name)
    End Sub
    
    'or 
    
    Private Sub EmployeeSortByDept_PreprocessQuery(ByRef query As System.Linq.IQueryable(Of LightSwitchApplication.Employees))
      query = query.OrderBy(Function(a) a.Department.Date)
    End Sub
    You can also use ".OrderByDescending" as well, if you need to reverse the order.


    Yann

    (plus ça change, plus c'est la même chose!)

    Saturday, August 6, 2011 3:55 AM
    Moderator
  • Hello again Yann,

    Sorry if I wasn't clear. What I am asking is how to sort by Name AND Date.

    I got it sorting by Name as above, but I couldn't figure out from intellisense how to do the equivalent of

    "ORDER BY Name, Date"

    Linq is next in my list of things to learn!

    Thanks!

    Alex

    Saturday, August 6, 2011 1:47 PM
  • Oh sorry about that. Well that's actually quite easy:

    Private Sub EmployeeSortByDept_PreprocessQuery(ByRef query As System.Linq.IQueryable(Of LightSwitchApplication.Employees))
     query = query.OrderBy(Function(a) a.Department.Name).ThenBy(Function(b) b.Department.Date)
    End Sub
    
    

    LINQ is *amazing* once you "get" it!


    Yann

    (plus ça change, plus c'est la même chose!)

    Saturday, August 6, 2011 1:52 PM
    Moderator
  • Ah! That did it! I kept trying to put things before the closing parenthesis, which gave me all kinds of other options.

    I'm looking forward to learning LINQ, but LS is first (looks like it may have to be concurrent!).

    Thank you!

    Alex

    Saturday, August 6, 2011 2:19 PM
  • OK, this worked great until I accidentally clicked on another column header, which made it sort by that column.

    Now it keeps sorting by that column even after I close that screen and re-open it, thereby ignoring my custom sort.

    How do I tell it to 'forget' that other column sort?

    Thanks!

    Alex

    Saturday, August 6, 2011 3:07 PM
  • Unfortunately, what you're seeing is a "by design" bug.

    It would be a good thing IF there were a way to set the sort back to what was designated by the query, but there isn't, at least not without deleting a folder in the user's "My Documents" folder.

    For each LS app, & each version of an LS app there is a corresponding folder in:

    "%UserProfile%\Documents\Microsoft\LightSwitch\Settings"

    Not something you want your user doing, but this is currently the *only* way to undo the user's sort selection.

    Well, you could make the grid not support user sort selection, but that's preventing, not undoing.


    Yann

    (plus ça change, plus c'est la même chose!)

    Sunday, August 7, 2011 12:16 AM
    Moderator
  • Thanks Yann, I turned off the sort.

    Alex

    Sunday, August 7, 2011 2:05 AM
  • No problem, glad I could at least point you in some kind of helpful direction, even if it's not "ideal".

    Yann

    (plus ça change, plus c'est la même chose!)

    Sunday, August 7, 2011 5:06 AM
    Moderator
  • You can fix this by adding a custom sort command to your UI:

    1. Add a boolean parameter called DoSort to your custom sort query.
    2. Add a boolean screen parameter and link it to the query parameter.
    3. Add a command with a new method that toggles the screen parameter, something like this:

    If DoSort Then DoSort = False Else DoSort = True
    

    By changing the value of the DoSort parameter, you will automatically refresh the screen and re-establish the default sort. You can add more parameters to this system in order to implement toggle sort or something more sophisticated (in this example we're not even using the DoSort parameter in the query, but we certainly could use it). The main point is you're giving the user a way to interact with the query logic.

     


    Paul Keister http://www.pjpm.biz
    Sunday, August 7, 2011 4:20 PM
  • Cool! Thank you! I will try that.

    Alex

    Sunday, August 7, 2011 5:11 PM
  • Paul,

    I thought this was quite a good idea, so I gave it a try.

    But it didn't change the user-chosen sort back for me.


    Yann

    (plus ça change, plus c'est la même chose!)

    Wednesday, August 10, 2011 4:21 AM
    Moderator
  • Hi, BTW disable grid sort at all and allowing to select explicit ViewBy... from UI combobox seems to be ok? Isnt always fine at all to allow clicking to columns for sorting by ANY field shown :-)
    Petr Antoš
    Tuesday, August 16, 2011 3:15 PM
  • Yes, you're absolutely right, just because the functionality is there doesn't mean that you *have* to make it available.

    It'd be nice if the functionality worked properly in the first place, but there's nothing wrong with providing your own options in combo boxes as you said.


    Yann

    (plus ça change, plus c'est la même chose!)

    Thursday, August 18, 2011 1:40 AM
    Moderator
  • @Yann:  +1 - there should be some way to disable the default column sort with Code so that the PreprocessQuery method will take precedence.  

    NOTE:  Even if you turn off the Collection Sort, you must first delete any store user settings before the order specified in PreprocessQuery will work.

    Friday, August 19, 2011 11:40 PM