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 
    }
}