Enhancements in Visual Studio 2012 for Sorting Data across Relationships (Michael Simons)

Enhancements in Visual Studio 2012 for Sorting Data across Relationships (Michael Simons)

Rate This
  • Comments 8

Sorting data across relationships is often required when building applications within LightSwitch.  For example, suppose you are building a LightSwitch application that attaches to the Category and Product tables from the Northwind database.

image

You can probably imagine applications in which you would want to display products and have them sorted by their associated Category’s CategoryName.  Within this blog post I will show you how this can be done with Visual Studio LightSwitch 2011 and how much easier it is to do with LightSwitch in Visual Studio 2012.

Visual Studio LightSwitch 2011

Suppose you want to create an editable grid screen that shows all of the Products sorted by the associated Category’s CategoryName then by the ProductName.  In order to accomplish this, you would think you could simply create a grid based on the Products EntitySet, and then within the Products screen query define a sort on both the Category.CategoryName as well as the ProductName.  Unfortunately this is not possible within Visual Studio LightSwitch 2011 because the query designer does not support defining sorts across relationships. 

image
Notice Category is absent within the field picker control of the query designer.

In order to define this sort you would need to create a modeled server query and write custom code within the query’s PreprocessQuery interception method.  You would then base your editable grid screen on this modeled server query.

C#

partial void ProductsByCategory_PreprocessQuery(ref IQueryable<Product> query)
{
    query = query.OrderBy(p => p.Category.CategoryName).ThenBy(p => p.ProductName);
}
  

VB

Private Sub ProductsByCategory_PreprocessQuery(ByRef query As System.Linq.IQueryable(Of LightSwitchApplication.Product))
    query = From p In query
            Order By p.Category.CategoryName, p.ProductName
End Sub

Now when you run this application and open this screen, you may notice that LightSwitch by default provides functionality that allows the end users to click on the column headers in order to define their own sorts.  This functionality is not enabled for all columns however.  For example, LightSwitch does not allow a user to define a sort on columns bound to computed properties or columns bound to an associated entity.  In this case that means the end user will not be able to sort on the Category column which displays the Summary Property of the associated Category entity.  The column’s tooltip as shown below will indicate whether or not it can be sorted.

image

LightSwitch in Visual Studio 2012

Within Visual Studio 2012 sorting on data across relationships is a lot easier.  The query designer now supports defining sorts across reference relationships.  This avoids having to write custom code.

image

Additionally if a reference property is displayed on a screen, the end user will be able to sort on it.  When the user clicks the column header, the data will be sorted on the summary property for the associated entity.  This is only supported if the summary property is something that LightSwitch considers sortable (i.e. it is not a computed, binary, or virtual reference property).

image
Notice the end user has clicked on the Category column header to sort the data by the Category’s CategoryName.

Conclusion

With LightSwitch in Visual Studio 2012 sorting across relationships has been expanded. It’s now a lot easier to set up and provides more functionality to the end user.

-Michael Simons, Visual Studio Team

Leave a Comment
  • Please add 7 and 1 and type the answer here:
  • Post
  • Brilliant! Lightswitch was first-class before, but you've made it even better in the new release!

    Thanks to everyone who worked so hard to produce Lightswitch!

  • How about be able to specify SortMemberPath for DataGrid control? Because for now the DataGrid control won't sort Reference property column.

  • Excellent!! That's what I was looking for....will try now.

  • Excellent!! That's what I was looking for....will try now.

  • Excellent!!

  • Really cool! Thanks for that :)

  • Fantastic, thanks for pointing this out.

  • What can we do if the lookup field has a specific order in which it should be displayed, other than alphabetical by its summary property - EG if the Categories table had a DisplayOrder int field which should be used as the sort order instead of the CategoryID or CategoryName.

    We can obviously set this up in the PreProcess Query event or the Query builder but that means we can't let the user sort the grid using the headers or our custom sort order will become alphabetical.

    It would be good if each table had a "Sort By" property which you could use to say how that table (and any related tables) will sort when the user clicks the grid column header.

Page 1 of 1 (8 items)