Share via

Entity Framework dynamic Order by (VB.NET)

  • Article

Introduction

A common requirement when working with data from a database is to provide the ability to sort by columns read from a database table which done in a Window desktop solution poses several challenges ranging from how to enable a DataGridView to properly sort as with conventional data sources like DataTable containers to providing dynamic sorting from selections in ComboBox and ListBox controls and optionally remember the last sort. 

Learn how to sort in a DataGridView and to present available columns in a ComboBox or ListBox dynamically where available columns are based off a standard or custom model which Entity Framework uses to contain table data.

Conventional OrderBy

When reading data which needs to be sorted a property within a specific model must be specified as shown below. Unlike conventional SQL where the column to sort is not a property of a class to sort by a column the column is appended to the SQL while with Entity Framework this is not possible without a method to dynamically creating the order by at runtime.

Which is equivalent to the following SQL

SELECT Cust.CustomerIdentifier, 
       Cust.CompanyName, 
       C.FirstName, 
       C.LastName, 
       Countries.Name
FROM Customers AS Cust
     INNER JOIN Contacts  AS  C ON  Cust.ContactId = C.ContactId
     INNER JOIN Countries  ON  Cust.CountryIdentifier = Countries.CountryIdentifier;

Dynamic Order By

There are two challenges for this at runtime.

  • Write code to present available columns in a model or DTO for a ListBox or ComboBox
  • Write a generic method which will work againsts a model or DTO which accepts a string name of a column to perform an order by
  • Consider that a view which joins several models together as it's rare to have a single table without the following so this means most likely the view may change and or property/column names may change so the code to perform order by must be fluid.
    • References table e.g. Country, Contact Type, Category etc.
    • Joined tabled e.g. Customers -> Orders

To accomplish this a lambda Expression and Func(Of T) can be used to build in this case a single column sort which by using slightly modified code could be written to perform one or more column sorts.

Written here as a language extension method:

Imports System.Linq.Expressions
Imports System.Runtime.CompilerServices
 
Namespace LanguageExtensions
 
    Public Module  GenericSorterExtension
        ''' <summary>
        ''' Specifies the sort direction against a property
        ''' </summary>
        Public Enum  SortDirection
            ''' <summary>
            ''' Sort ascending.
            ''' </summary>
            Ascending
            ''' <summary>
            ''' Sort descending.
            ''' </summary>
            Descending
        End Enum
 
        <Extension>
        Public Function  Sort(Of T)(list As  List(Of T), propertyName As String, sortDirection As  SortDirection) As  List(Of T)
 
            Dim param As ParameterExpression = Expression.Parameter(GetType(T), "item")
 
            Dim sortExpression As Expression(Of Func(Of T, Object)) = Expression.Lambda(Of Func(Of T, Object))(
                Expression.Convert(
                    Expression.Property(param, propertyName), GetType(Object)), param)
 
            Select Case  sortDirection
                Case SortDirection.Ascending
                    list = list.AsQueryable().OrderBy(sortExpression).ToList()
                Case Else
                    list = list.AsQueryable().OrderByDescending(sortExpression).ToList()
            End Select
 
            Return list
 
        End Function
 
    End Module
End Namespace

To get column names which can be passed to the language extension above.

''' <summary>
''' Get names of properties for CustomerItem which over
''' time CustomerItem have have properties added or removed
''' so this handles any changes rather than hard coded properties
''' or property name changes.
''' </summary>
''' <returns></returns>
Public Function  CustomerItemPropertyNames() As List(Of String)
    Dim list As New  List(Of String)
 
    Dim customerItemType As Type = GetType(CustomerItem)
 
    For Each  propertyInfo As  PropertyInfo In
        customerItemType.GetProperties(BindingFlags.Instance Or
                                       BindingFlags.Public Or  BindingFlags.NonPublic)
 
        list.Add(propertyInfo.Name.SplitCamelCase())
 
    Next
 
    Return list
 
End Function

For this example there is a Customer model, Contact model and Country model. The following class provides a view for two Customer properties, two Contact properties and one Country property.

''' <summary>
''' Default view for presenting data
''' </summary>
''' <remarks>
''' Important, how things are setup calling context.Customers.ToList
''' will fail as FirstName and LastName are not part of the model.
''' </remarks>
Partial Public  Class Customer
    Public Property  FirstName As  String
    Public Property  LastName As  String
 
    Public Shared  ReadOnly Property  Projection() As  Expression(Of Func(Of Customer, CustomerItem))
 
        Get
            Return Function(customer) New  CustomerItem() With  {
                .CustomerIdentifier = customer.CustomerIdentifier,
                .CompanyName = customer.CompanyName,
                .CountryName = customer.Country.Name,
                .FirstName = customer.Contact.FirstName,
                .LastName = customer.Contact.LastName}
        End Get
 
    End Property
End Class

A ComboBox is populated with column/property names as follows.

ColumnNameListBox.DataSource = operations.CustomerItemPropertyNames()

In the screenshot below note the ComboBox populated with column names, below the ComboBox there is a CheckBox which specifies the sort direction, default is ascending. 

To perform the sort the selected item in the ComboBox is passed to the CustomSort method.

There may be several parts of this code new to some developers.

  • The method is asynchronous which ensure the front end remains responsive. This is important for a good user experience.
  • How the lambda select is constructed. By creating a read only property the view can be easily maintained and used over and over again.

By following these steps for any model one column sorting is easy. There is a caveat, once data is presented into a DataGridView column sorting will not work. To enable sorting use the following component BindingListView which may be installed using NuGet package manager.

Install-Package Equin.ApplicationFramework.BindingListView

To use the BindingList, setup a private variable in form.

Private CustomerView As BindingListView(Of CustomerItem)

Replace CustomerItem with the class/model to work with in your project.

Load returning data into a variable e.g.

By following the steps above dynamic order by can be very easy.

Multiple columns

To provide multiple property/column sort the following requires a string array of property names and a sort direction. The enum for sort direction is done a different way then the order by for a single property/column, some may like this while other prefer the latter.

Note in the language extension the direction for ordering is the same for both properties.

<Extension>
Public Function  SortMultiColumn(Of T)(source As IQueryable(Of T),
    propertyNames() As  String, sortOrder As SortDirection) As IOrderedQueryable(Of T)
 
    If propertyNames.Length = 0 Then
        Throw New  InvalidOperationException()
    End If
 
    Dim param = Expression.Parameter(GetType(T), String.Empty)
    Dim expressionPropField = Expression.PropertyOrField(param, propertyNames(0))
 
    Dim sortExpression = Expression.Lambda(expressionPropField, param)
 
    Dim orderByCall As MethodCallExpression = Expression.Call(GetType(Queryable), "OrderBy" &
        ((If(sortOrder = SortDirection.Descending, "Descending",  String.Empty))),
            {GetType(T), expressionPropField.Type},
                source.Expression, Expression.Quote(sortExpression))
 
    If propertyNames.Length > 1 Then
        For index As Integer  = 1 To  propertyNames.Length - 1
            Dim item = propertyNames(index)
            param = Expression.Parameter(GetType(T), String.Empty)
            expressionPropField = Expression.PropertyOrField(param, item)
 
            sortExpression = Expression.Lambda(expressionPropField, param)
 
            orderByCall = Expression.Call(GetType(Queryable), "ThenBy" &
                ((If(sortOrder = SortDirection.Descending, "Descending",  String.Empty))),
                    {GetType(T), expressionPropField.Type},
                        orderByCall, Expression.Quote(sortExpression))
        Next
    End If
 
 
    Return DirectCast(source.Provider.CreateQuery(Of T)(orderByCall), IOrderedQueryable(Of T))
 
End Function

The following is a wrapper for the extension method above.

Public Async Function CustomerSortTwoProperties(
    propertyName As  String(),
    Optional SortDirection As SortDirection = SortDirection.Descending) As Task(Of List(Of CustomerItem))
 
    Using context = New  NorthWindAzureContext()
        Return Await Task.Run(
            Function()
                Return context.Customers.
                                 Select(Customer.Projection).
                                 SortMultiColumn(propertyName, SortDirection).ToList()
            End Function)
    End Using
 
End Function

To implement the two property order by in a form.

Imports WindowsApp1.Classes
Imports WindowsApp1.LanguageExtensions
 
Public Class  MultiColumnSortForm
    Private operations As New  Operations
 
    Private Async Sub MultiColumnSortForm_Shown(sender As Object, e As  EventArgs) Handles  Me.Shown
        Dim results = Await operations.
                CustomerSortTwoProperties({"CompanyName", "CountryName"},
                                          SortDirection.Descending)
 
        DataGridView1.DataSource = results
    End Sub
End Class
IMPORTANT
In the language extension presented in this article should not require any modifications so do not get hung up on the complexities of the code but instead simply use them. 

Unusual order by

Even though this does not handle dynamic order (but could with some refactors) by it's worth mentioning an OrderBy can use a IComparer to provide specialized ordering.

For example, a business rule states that specific company names should appear at the top of the order then the remaining company names to follow.

The comparer

Namespace Classes
    Public Class  SpecificOrdering
        Inherits OrderedPredicateComparer(Of String)
 
        Private Shared  ReadOnly Order() As Func(Of String, Boolean) = {
            Function(value) value.StartsWith("Bon"),
            Function(item) item.StartsWith("Cac"),
            Function(item) item.StartsWith("Du")}
 
        Public Sub  New()
            MyBase.New(Order)
        End Sub
    End Class
End Namespace

Usage in a project

  1. Copy GenericSorterExtensions.vb to your project, all code within is generic.
  2. Create a DTO class as per CustomerItem.
  3. Setup a project as shown in the partial class Customer.
  4. Write code in a form to get the data, use Form1 code as a guide.

Summary

Code presented to assist in learning how to provide dynamic ordering of properties for an Entity Framework model in which string values are used as properties where the string values for column names are evaluated so that if a column/property name changes they will be reflected at runtime.

There are modifications which may be done to suit business needs without touching the generic methods used to create the order by’s.

See also

ASP.NET Core (C#) Blazor sorting

Source code

The following GitHub repository see this project while a secondary class project is not needed. The secondary class project will be part of another article.