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?

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