DataGridView: Sort with List DataSource
Introduction
By default, a DataGridView populated using its DataSource property does not support sorting, to enable sorting the DataSource of the DataGridView needs to coerce the DataSource into a list that supports sorting (IBindingList and IBindingListView), and then it will work out of the box. An exploration will cover how to enable sorting using Entity Framework 6 code first with an existing database and will work without Entity Framework using LINQ to SQL.
Business requirements
Read data from several tables using Visual Basic and Entity Framework 6 with joins into a List<T> where T contains data from three tables and display the list in a DataGridView which allows users to click column headers to sort A-Z and Z-A.
DbContext model
Namespace EntityClasses
Partial Public Class NorthWindAzureContext
Inherits DbContext
Public Sub New()
MyBase.New("name=NorthWindAzureContext")
End Sub
Public Overridable Property Contacts As DbSet(Of Contact)
Public Overridable Property ContactTypes As DbSet(Of ContactType)
Public Overridable Property Countries As DbSet(Of Country)
Public Overridable Property Customers As DbSet(Of Customer)
Protected Overrides Sub OnModelCreating(modelBuilder As DbModelBuilder)
modelBuilder.Entity(Of Contact)() _
.HasMany(Function(e) e.Customers) _
.WithOptional(Function(e) e.Contact) _
.WillCascadeOnDelete()
modelBuilder.Entity(Of ContactType)() _
.HasMany(Function(e) e.Customers) _
.WithOptional(Function(e) e.ContactType) _
.WillCascadeOnDelete()
End Sub
End Class
End Namespace
Data class
Namespace Classes
Public Class CustomerEntity
Public Property CustomerIdentifier() As Integer
Public Property CompanyName() As String
Public Property ContactIdentifier() As Integer?
Public Property FirstName() As String
Public Property LastName() As String
Public Property ContactTypeIdentifier() As Integer
Public Property ContactTitle() As String
Public Property Address() As String
Public Property City() As String
Public Property PostalCode() As String
Public Property CountryIdentifier() As Integer?
Public Property CountyName() As String
Public Overrides Function ToString() As String
Return $"{CompanyName}"
End Function
End Class
End Namespace
Read data class
Public Class Operations
''' <summary>
''' Read in data from the Customer table joining in Contact and ContactType tables.
''' </summary>
''' <param name="context">DbContext created in the main form</param>
''' <returns>List(Of CustomerEntity)</returns>
Public Function Customers(context As NorthWindAzureContext) As List(Of CustomerEntity)
Dim customerEntityList As List(Of CustomerEntity) = (
From customer In context.Customers
Join contactType In context.ContactTypes On customer.ContactTypeIdentifier _
Equals contactType.ContactTypeIdentifier
Join contact In context.Contacts On customer.ContactId Equals contact.ContactId
Select New CustomerEntity With {
.CustomerIdentifier = customer.CustomerIdentifier,
.CompanyName = customer.CompanyName,
.ContactIdentifier = customer.ContactId,
.FirstName = contact.FirstName,
.LastName = contact.LastName,
.ContactTypeIdentifier = contactType.ContactTypeIdentifier,
.ContactTitle = contactType.ContactTitle,
.Address = customer.Address,
.City = customer.City,
.PostalCode = customer.PostalCode,
.CountryIdentifier = customer.CountryIdentifier,
.CountyName = customer.Country.Name
}).ToList()
Return customerEntityList
End Function
End Class
Default implementation no sorting
Using the containers above along with the read operation, place a DataGridView on a form, add column for each property to display and set DataPropertyName to the backing property which is done in the DataGridView designer.
Imports EntityFramework6Example.Classes
Imports EntityFramework6Example.EntityClasses
Namespace Forms
Public Class NoSortingByDefaultForm
Private Operations As Operations = New Operations
Private context As NorthWindAzureContext = New NorthWindAzureContext()
Private BindingSource As BindingSource = New BindingSource()
Private Sub Form1_Shown(sender As Object, e As EventArgs) Handles Me.Shown
gridView.AutoGenerateColumns = False
BindingSource.DataSource = Operations.Customers(context)
gridView.DataSource = BindingSource
gridView.ExpandColumns()
Label1.Text = $"Supports sorting: {BindingSource.SupportsSorting}"
End Sub
End Class
End Namespace
The last line in form shown event determines if the underlying data source supports sorting which it does not as the underlying source does not implement IComparable(Of T). When clicking on a column header nothing happens since sorting is not supported.
Classes to DataTable with sorting
When loading data without Entity Framework using a connection and command object where the command object has a SELECT statement which is loaded into a DataTable then use the DataTable as the DataSource of a DataGridView since the DataTable will take care of sorting capabilities clicking column headers in a DataGridView will provide sorting.
Knowing this an option is to use reflection to convert a list read using Entity Framework which provides sorting in a DataGridView using the following language extension.
<Extension>
Public Function ToDataTable(Of T)(data As IList(Of T)) As DataTable
Dim properties As PropertyDescriptorCollection = TypeDescriptor.GetProperties(GetType(T))
Dim table As New DataTable()
For Each prop As PropertyDescriptor In properties
table.Columns.Add(prop.Name, If(Nullable.GetUnderlyingType(prop.PropertyType), prop.PropertyType))
Next
For Each item As T In data
Dim row As DataRow = table.NewRow()
For Each prop As PropertyDescriptor In properties
row(prop.Name) = If(prop.GetValue(item), DBNull.Value)
Next
table.Rows.Add(row)
Next
Return table
End Function
Using the same setup as above add .ToDataTable onto the read method.
Public Class ConvertSourceToSortForm
Private Operations As Operations = New Operations
Private context As NorthWindAzureContext = New NorthWindAzureContext()
Private BindingSource As BindingSource = New BindingSource()
Private Sub Form1_Shown(sender As Object, e As EventArgs) Handles Me.Shown
gridView.AutoGenerateColumns = False
BindingSource.DataSource = Operations.Customers(context).ToDataTable()
gridView.DataSource = BindingSource
gridView.ExpandColumns()
Label1.Text = $"Supports sorting: {BindingSource.SupportsSorting}"
End Sub
End Class
In this case Label1 will indicate sorting is supported.
Going this route circumvents using Entity Framework as extra steps are required to pass changes back to the database along with DataTable interaction is untyped while Entity Framework is typed.
Inject custom BindingList
To keep with a clean Entity Framework solution the following BindingList provides sorting. This BindingList may be found on the web in various states were in some cases has issues along with no clear example on usage.
Imports System.ComponentModel
Imports System.Reflection
Public Class SortableBindingList(Of T)
Inherits BindingList(Of T)
Private Property IsSorted As Boolean
Private Property SortDirection As ListSortDirection
Private Property SortProperty As PropertyDescriptor
Protected Overrides ReadOnly Property SupportsSortingCore() As Boolean
Get
Return True
End Get
End Property
Protected Overrides ReadOnly Property SortDirectionCore() As ListSortDirection
Get
Return _SortDirection
End Get
End Property
Protected Overloads Overrides ReadOnly Property SortPropertyCore() As PropertyDescriptor
Get
Return _SortProperty
End Get
End Property
Protected Overloads Overrides Sub ApplySortCore(
Descriptor As PropertyDescriptor,
Direction As ListSortDirection)
Dim items = TryCast(Me.Items, List(Of T))
If items Is Nothing Then
IsSorted = False
Else
Dim pCom As New PCompare(Of T)(Descriptor.Name, Direction)
items.Sort(pCom)
IsSorted = True
SortDirection = Direction
SortProperty = Descriptor
End If
OnListChanged(New ListChangedEventArgs(ListChangedType.Reset, -1))
End Sub
Protected Overloads Overrides ReadOnly Property IsSortedCore() As Boolean
Get
Return _IsSorted
End Get
End Property
Protected Overrides Sub RemoveSortCore()
_IsSorted = False
End Sub
#Region " Constructors "
Sub New(ByVal list As ICollection(Of T))
MyBase.New(CType(list, IList(Of T)))
End Sub
Sub New()
MyBase.New()
End Sub
#End Region
#Region " Property comparer "
Private Class PCompare(Of T)
Implements IComparer(Of T)
Private Property PropInfo As PropertyInfo
Private Property SortDir As ListSortDirection
Friend Sub New(sortProperty As String, sortDirection As ListSortDirection)
PropInfo = GetType(T).GetProperty(sortProperty)
SortDir = sortDirection
End Sub
Friend Function Compare(value1 As T, value2 As T) As Integer Implements IComparer(Of T).Compare
Return If(SortDir = ListSortDirection.Ascending,
Comparer.[Default].Compare(PropInfo.GetValue(value1, Nothing),
PropInfo.GetValue(value2, Nothing)),
Comparer.[Default].Compare(PropInfo.GetValue(value2, Nothing),
PropInfo.GetValue(value1, Nothing)))
End Function
End Class
#End Region
End Class
Usage, add this class into your project then as shown below
- Create a BindingSource with the DataSource set to a new instance of the SortableBindingList feeding in data from Entity Framework which is enough to present data read from the a database.
- Create a instance of the SortableBindingList, assign it strongly typed from the BindingSource above. This provides access to data later in the application lifetime such as accessing the current row in a DataGridView.
Public Class GenericSortForm
Private Operations As Operations = New Operations
Private context As NorthWindAzureContext = New NorthWindAzureContext()
Private BindingSource As BindingSource = New BindingSource()
Private BindingCustomers As SortableBindingList(Of CustomerEntity)
Private Sub Form1_Shown(sender As Object, e As EventArgs) Handles Me.Shown
gridView.AutoGenerateColumns = False
'
' This is all that is needed to display data
'
BindingSource.DataSource =
New SortableBindingList(Of CustomerEntity)(Operations.Customers(context))
'
' This allow access to individual rows and properties
'
BindingCustomers = CType(BindingSource.DataSource, SortableBindingList(Of CustomerEntity))
gridView.DataSource = BindingSource
gridView.ExpandColumns()
Label1.Text = $"Supports sorting: {BindingSource.SupportsSorting}"
End Sub
End Class
In this case Label1 will indicate sorting is supported.
Implement in your project
Rather than copying the SortableBindingList into a project.
- Create a new Visual Studio solution
- Copy the class project in this articles source code into the root folder of the solution above
- Build the solution
- Open up a Visual Studio solution where to use the SortableBindingList
- Under project references, add a reference to the DLL created in bullet 1 which will copy the DLL to the current project ready to use.
Preparing supplied Visual Studio solution
- Create a new database named NorthWindAzureForInserts best done in SSMS (SQL-Server Management Studio)
- Run DataScripts DataScripts.sql under the new database to create and populate tables
- Under Solution Explorer, right click
- Select "Restore NuGet packages"
- Build the solution
- Run the project, there are three buttons to test out what has been shown.
- First button has no sorting
- Second button works with a DataTable via Entity Framework using a language extension
- Pure Entity Framework using SortableBindingList.
Summary
Two options have been presented to enable sorting of data in a List(Of T) read from Entity Framework 6. Depending on business requirements will dictate which option to choose while the better choice is always going to be using the sortable BindingSource as using this option does not inject a secondary data container into code which now needs a translator to work between a DataGridView, Entity Framework and the backend database tables.
See also
Source code
Complete source code with scripts to create a database, tables and populate the tables