Allow user to sort columns from a LINQ query in a DataGridView

Question

I can't quite work out how to allow a DataGridView populated at runtime to sort (when users click on the column headers) where a LINQ from XML query is the DataSource, via a BindingSource.

    Dim QueryReOrder = From Q In Query _
                       Where ((0 - Q.Qualifier) / cmbTSStakeValue.Text) <= 0.1 _
                       Order By Q.Qualifier Descending _
                       Select Q

    Dim bs As New BindingSource
    bs.DataSource = QueryReOrder
    DGFindMatch.DataSource = bs

Some of the DataGridView's properties are:

Sort            Nothing String
SortProperty                Nothing System.ComponentModel.PropertyDescriptor
SupportsAdvancedSorting         False   Boolean
SupportsChangeNotification      True    Boolean
SupportsFiltering           False   Boolean
SupportsSearching           False   Boolean
SupportsSorting             False   Boolean

Is there a simple solution that will allow a user to be able to sort these values by clicking the column header?

Thanks!

Answer 1

You need to get the results of the LINQ query into something supports sorting functionality. This is typically done by deriving a class from BindingList and implementing the Sorting Core functionality in the derived class.

There are many examples of implementations out there to choose from and it is a pretty straight forward thing to implement. Here is an example of doing it on MSDN.

Once you have this implemented all you have to do is put your results in it and use it as your DataSource and the Grid should allow users to sort using the columns.

    //I know that you asked the question in VB.NET but I don't know the syntax that well.
    public class SortableBindingList<T> : BindingList<T>
    {
         //override necessary sort core methods
    }

    SortableBindingList<string> list = new SortableBindingList<string>(QueryReOrder.ToList());

    //use list as your DataSource now

Answer 2

My default approach is to copy everything into a DataTable and bind the DataGridView to that.

Obviously that won't work well if you want to add paging.

Answer 3

You need to get the query results as AsEnumerable().

Dim QueryReOrder = (From Q In Query _ Where ((0 - Q.Qualifier) / cmbTSStakeValue.Text) <= 0.1 _ Order By Q.Qualifier Descending _ Select Q).AsEnumerable()

I should mention I'm usually in C# so it's possible you'll have to vary the syntax slightly.

Answer 4

Ya, so I struggled with this for awhile. All the same answers about creating a custom generic IBindingList for each class. That's a crazy amount of work to do if the columns in your grid views are not static. I want to be able to change my linq queries and not have to change or update a class that implements the custom IBindingList. So, here's what I did:

1) Get your IEnumerable query.

var query = from o in m_ds.Objective

        join ot in m_ds.ObjectiveType on o.ObjectiveTypeId equals ot.Id
        join dst in m_ds.DevelopmentStatusType on o.DevelopmentStatusTypeId equals dst.Id
        join rt in m_ds.ResultType on o.PrecedenceResultTypeId equals rt.Id

        select new
        {
            o.Id,
            type = ot.Description,
            precedence = rt.Description,
            o.Symbol,
            o.Title,
        };

2) Convert that IEnumerable result set to a DataTable!

public static DataTable DataTableFromIEnumerable( IEnumerable ien )
{
    DataTable dt = new DataTable();
    foreach ( object obj in ien )
    {
        Type t = obj.GetType();
        PropertyInfo[] pis = t.GetProperties();
        if ( dt.Columns.Count == 0 )
        {
            foreach ( PropertyInfo pi in pis )
            {
                dt.Columns.Add( pi.Name, pi.PropertyType );
            }
        }

        DataRow dr = dt.NewRow();
        foreach ( PropertyInfo pi in pis )
        {
            object value = pi.GetValue( obj, null );
            dr[ pi.Name ] = value;
        }

        dt.Rows.Add( dr );
    }

    return dt;
}

3) Bind your DataGridView to that generic DataTable object.

var query = SqlHelper.GetFilteredObjective();
var bs = new BindingSource();
bs.DataSource = Utils.DataTableFromIEnumerable( query );
dgvObjectives.DataSource = bs;

4) That's it. One utility function and you're friggin' done :)

Props to Alberto Poblacion who wrote the above function to go from an IEnumerable to a DataTable: function thread

c# datagridview sortable linq to ADO.NET

Answer 5

Another link that gives a complete example of how to construct a SortableBindingList, as described in Brian ONeil's answer, can be found here:

Sortable Binding List for custom data objects

I was able to use this example almost verbatim.