Entity Framework: How to make DataGridView Sortable?

Question

How do I make a DataGridView sort-able when using Entity Framework to pull data from a database?

I'm putting the query into the DataSource of the DataGridView.

Dim Query = (From t In DB.interview_task Where t.CONTROL = CONTROL And t.CLIENTCODE = CLIENTCODE Order By t.StartDate Descending
            Select t.ID, t.CONTROL, t.CLIENTCODE, t.TaskType, t.Title, t.StartDate, t.DueUserName, Status = If(t.CompleteDate Is Nothing, "In Progress", "Completed")).ToList

dgvTaskList.DataSource = Query

The only way to load data into a DGV is by turning it into a .List but this makes the grid unsortable.

The examples I'm seeing on Google are outdated or really complicated. This feels like something that should be simple. I was dumping the query into a DataTable but I get back Time which isn't on the column.

So how do I put an EF Query on a DGV and make it sort-able?

Update:

So I was able to get it to work using Karen's Answer, I did the following;

Public Sub Load_TaskList()
    Using DB As New wotcDB
        Dim Query2 = From t In DB.interview_task Where t.CONTROL = CONTROL And t.CLIENTCODE = CLIENTCODE Order By t.StartDate Descending
                     Select New TaskList With {.ID = t.ID,
                         .CONTROL = t.CONTROL,
                         .CLIENTCODE = t.CLIENTCODE,
                         .TaskType = t.TaskType,
                         .Title = t.Title,
                         .StartDate = t.StartDate,
                         .Status = If(t.CompleteDate Is Nothing, "In Progress", "Completed")}
        dgvTaskList.DataSource = New WOTC_Common.SortableBindingList(Of TaskList)(Query2.ToList)
    End Using
    dgvTaskList.Columns("id").Visible = False
    dgvTaskList.Columns("CONTROL").Visible = False
    dgvTaskList.Columns("CLIENTCODE").Visible = False
End Sub

Class TaskList
    Public Property ID As Integer
    Public Property CONTROL As Integer
    Public Property CLIENTCODE As String
    Public Property TaskType As String
    Public Property Title As String
    Public Property StartDate As Date?
    Public Property DueUserName As String
    Public Property Status As String
End Class

So for another question. Is it possible to use this sorting method without having to declare TaskList?

Call Sort(...)? Just looking at the documentation for DataGridView, I see a ColumnHeaderMouseClickEvent. Why not listen to it and call Sort for that column? That seems to be the simplest. msdn.microsoft.com/en-us/library/0868ft3z(v=vs.110).aspx — TyCobb
– TyCobb, Commented Jan 13, 2017 at 15:45
In regards to not using TaskList, no, the SortableBindingList needs a strong type. — Karen Payne
– Karen Payne, Commented Jan 13, 2017 at 20:31
Bummer, anyway your answer worked. So Marked as Answer. Thank you. — Kayot
– Kayot, Commented Jan 13, 2017 at 21:04

Karen Payne · Accepted Answer · 2017-01-13 17:37:56Z

1

Use SortableBindingList. Create and set it up then assign it to a BindingSource and assign the BindingSource to the DataGridView. Sorry my only example (and easy to follow) is in C# in a MSDN code sample I did for EF6 in Windows forms.

The SortableBindingList https://code.msdn.microsoft.com/windowsdesktop/Generic-sortable-binding-47cac3cc

My code sample, https://code.msdn.microsoft.com/Entity-Framework-in-764fa5ba

Download the class in the first link, look at the code in Form1, load event where blCustomers is set to a entity Customers then the SortableBindingList is set to bsCustomers a BindingSource and finally bsCustomers becomes the DataSource for the DataGridView. If you need this in VB.NET I can put one together later, currently VS2015 on my machine is updating.

Update Here I get data from my entity using a simple select and use a class to strong type the data. The BindingSource is optional but I like the functionality it provides. Note in Button1 I cast Current property of the BindingSource to DemoClass and get the two properties.

Public Class Form1
    Private bsCustomers As New BindingSource
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        Using entity As New DemoEntities
            Dim results = entity _
                .Customers _
                .Select(Function(items) New DemoClass With
                {
                    .Id = items.id,
                    .LastName = items.LastName
                }
            ).ToList
            bsCustomers.DataSource = New SortableBindingList(Of DemoClass)(results)
            DataGridView1.DataSource = bsCustomers

        End Using
    End Sub
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim LastName As String = CType(bsCustomers.Current, DemoClass).LastName
        Dim Identifier As Integer = CType(bsCustomers.Current, DemoClass).Id
        MessageBox.Show($"id: {Identifier} Lastname: {LastName}")
    End Sub
End Class
Class DemoClass
    Public Property Id As Integer
    Public Property LastName As String
End Class

Note the syntax for the MessageBox content is VS2015, for a lower version use String.Format.

edited Jan 13, 2017 at 17:37

answered Jan 13, 2017 at 16:14

Karen Payne

5,3173 gold badges20 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Kayot Over a year ago

I've imported this into my code. How do I use anonymous types with this Sortable Binding List? I've never used T before and I don't know how it works.

Karen Payne Over a year ago

See my updated reply where I just created a sample that I kept simple for clarity. The class used for sorting is the one I mentioned in my reply.

Tam88 Over a year ago

@KarenPayne , Hello, I have a post link ,that uses the your extension method ,SortableBindingList(Of T As Class) maybe you can help me?

Robin Bennett · Accepted Answer · 2022-08-17 16:37:51Z

Just in case anyone else wants a generic SortableBindingList in VB.Net, here's my translation of mjhillman's C# answer from this question:

C# - troubles with sorting datagridview

Imports System.ComponentModel

Public Class SortableBindingList(Of T As Class)
    Inherits BindingList(Of T)

    Private _isSorted As Boolean
    Private _sortDirection As ListSortDirection = ListSortDirection.Ascending
    Private _sortProperty As PropertyDescriptor

    Public Sub New()
    End Sub

    Public Sub New(list As IList(Of T))
        MyBase.New(list)
    End Sub

    Protected Overrides ReadOnly Property SupportsSortingCore As Boolean
        Get
            Return True
        End Get
    End Property

    Protected Overrides ReadOnly Property IsSortedCore As Boolean
        Get
            Return _isSorted
        End Get
    End Property

    Protected Overrides ReadOnly Property SortDirectionCore As ListSortDirection
        Get
            Return _sortDirection
        End Get
    End Property

    Protected Overrides ReadOnly Property SortPropertyCore As PropertyDescriptor
        Get
            Return _sortProperty
        End Get
    End Property


    Protected Overrides Sub RemoveSortCore()
        _sortDirection = ListSortDirection.Ascending
        _sortProperty = Nothing
        _isSorted = False
    End Sub

    Protected Overrides Sub ApplySortCore(prop As PropertyDescriptor, direction As ListSortDirection)
        _sortProperty = prop
        _sortDirection = direction

        Dim list As List(Of T) = CType(Items, List(Of T))
        If list Is Nothing Then Return
        list.Sort(AddressOf Compare)
        _isSorted = True
        'fire an event that the list has been changed.
        OnListChanged(New ListChangedEventArgs(ListChangedType.Reset, -1))
    End Sub

    Private Function Compare(lhs As T, rhs As T) As Integer
        Dim result = OnComparison(lhs, rhs)
        'invert if descending
        If _sortDirection = ListSortDirection.Descending Then
            result = -result
        End If
        Return result
    End Function

    Private Function OnComparison(lhs As T, rhs As T) As Integer

        Dim lhsValue = If(IsNothing(lhs), Nothing, _sortProperty.GetValue(lhs))
        Dim rhsValue = If(IsNothing(rhs), Nothing, _sortProperty.GetValue(rhs))

        If IsNothing(lhsValue) Then
            Return If(IsNothing(rhsValue), 0, -1) 'nulls are equal
        End If

        If IsNothing(rhsValue) Then
            Return 1 'first has value, second doesn't
        End If

        If TypeOf lhsValue Is IComparable Then
            Return CType(lhsValue, IComparable).CompareTo(rhsValue)
        End If

        If lhsValue.Equals(rhsValue) Then
            Return 0 'both are the same
        End If

        'Not comparable, compare ToString
        Return lhsValue.ToString().CompareTo(rhsValue.ToString())
    End Function
End Class

Collectives™ on Stack Overflow

Entity Framework: How to make DataGridView Sortable?

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related