How to sort DataGridView when bound to a binding source that is linked to an EF4 Entity

I have a DataGridView that is linked t开发者_开发问答o a BindingSource.

My BindingSource is linked to an IQueryable list of entities:

    public void BindTo(IQueryable elements)
    {
        BindingSource source = new BindingSource();
        source.DataSource = elements;

        bindingNavigator1.BindingSource = source;
        dataGridView1.DataSource = source;

    }

I am wanting my users to be able to click on the grid headers to sort the data - struggling to get this to work. Is it possible? If so, how do I do it?

I recently struggled with this same issue; it seems that the IQueryable interface doesn't provide enough information for the DataViewGrid to know how to sort the data automatically; so you have to either repackage your collection from the Entity source using something it can use or do what I did and handle the sorting functionality manually:

      private void myDataGridView_ColumnHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)
  {
     DataGridViewColumn column = myDataGridView.Columns[e.ColumnIndex];

     _isSortAscending = (_sortColumn == null || _isSortAscending == false);

     string direction = _isSortAscending ? "ASC" : "DESC";

     myBindingSource.DataSource = _context.MyEntities.OrderBy(
        string.Format("it.{0} {1}", column.DataPropertyName, direction)).ToList();

     if (_sortColumn != null) _sortColumn.HeaderCell.SortGlyphDirection = SortOrder.None;
     column.HeaderCell.SortGlyphDirection = _isSortAscending ? SortOrder.Ascending : SortOrder.Descending;
     _sortColumn = column;
  }

I hope that helps.

VB.NET

If you are using a bindingsource with linq syntax you can sort your data like this

In this case When loading a bindningsource associated with a datagridview from entity framwork objects "NCFile" with having a foreign column to a list of "NCFilePartSet "

bsFileSections.DataSource = From ncfps In NCFile.NCFilePartSet Order By ncfps.Sort Select ncfps 

or like this

bsFileSections.DataSource = NCFile.NCFilePartSet.OrderBy(Function(ncfps) ncfps.Sort)

where "Sort" is a column in NCFilePartSet

Updates on entities continue working and reflects back to the database

Yes, it is possible to easily have a sortable DGV when bound to EF data. Use the BindingListView from the BLW library (also, check out How do I implement automatic sorting of DataGridView?).

public void BindTo(IQueryable elements)
{
    BindingSource source = new BindingSource();
    source.DataSource = new BindingListView(elements.ToList());

    bindingNavigator1.BindingSource = source;
    dataGridView1.DataSource = source;

}

In my tests, even when .ToList() was called within the constructor (as above), changes were propagated to the DB, which surprised me.

This code snippet works very well, and fast enough for most purposes...

int iColNumber = 3; //e.g., sorting on the 3rd column of the DGV

MyBindingSource.DataSource = MyBindingList.OrderByDescending(o => o.GetType().GetProperty(MyDataGridView.Columns[iColNumber].Name).GetValue(o));

I suggest you to dynamically convert entities into data table. There is a method

 public static DataTable GetTableFromList<T>(IEnumerable<T> list)
    {
        DataTable dt = new DataTable();
        PropertyInfo[] properties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

        foreach (PropertyInfo property in properties)
            dt.Columns.Add(property.Name, property.PropertyType);

        foreach (T item in list)
        {
            DataRow newRow = dt.NewRow();
            foreach (PropertyInfo property in properties)
            {
                newRow[property.Name] = property.GetValue(item);
            }
                dt.Rows.Add(newRow);
        }

        return dt;
    }

After conversion pass table into bindingsource

source.DataSource = GetTableFromList<T>(elements);

Be aware of property types that cannot be converted into a column types.

maybe this will help you.

internal class CustomDataGridView : DataGridView
{   
    public SortOrder MySortOrder { get; set; }
    protected override void OnColumnHeaderMouseClick(DataGridViewCellMouseEventArgs e)
    {
        BindingSource MyBindingSource = (BindingSource)base.DataSource;
        DataTable MyDataTable = (DataTable)MyBindingSource.DataSource;
        switch (MySortOrder)
        {
            case SortOrder.None:
                MyDataTable.DefaultView.Sort = base.Columns[e.ColumnIndex].Name + " ASC";
                MyDataTable = MyDataTable.DefaultView.ToTable();
                MyBindingSource.DataSource = MyDataTable;
                MySortOrder = SortOrder.Ascending;
                break;

            case SortOrder.Ascending:
                MyDataTable.DefaultView.Sort = base.Columns[e.ColumnIndex].Name + " DESC";
                MyDataTable = MyDataTable.DefaultView.ToTable();
                MyBindingSource.DataSource = MyDataTable;
                MySortOrder = SortOrder.Descending;
                break;

            case SortOrder.Descending:
                MyDataTable.DefaultView.Sort = Properties.Settings.Default.OderDataGridView; //SqlOriginOrder
                MyDataTable = MyDataTable.DefaultView.ToTable();
                MyBindingSource.DataSource = MyDataTable;
                MySortOrder = SortOrder.None;
                break;
        }
        base.Columns[e.ColumnIndex].HeaderCell.SortGlyphDirection = MySortOrder; //mini arrow 
    }
}