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

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.


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. 

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.


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


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.


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.


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).


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


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