Datagridview with sorted EntitySet<T>? (.NET 3.5)

Question

I'm hoping there's a simple answer to this one I've missed in hours of Googling...

I have a DataGridView which needs to display and add/delete/edit records from a database. I'm using the Entity Framework, so records are initially EntitySet.

• Attempt One

BindingSource has facilities for sorting, but oh... they don't seem to actually be available if your DataSource is an EntitySet.

• Attempt Two

Try and implement custom sorting on the grid, but oh... custom sorting not allowed if the grid is databound.

• Attempt Three

Peform a Linq query on my data and use the order function there. This works to get my ordered data, but oh... it changed AllowNew to false. I can force it back, but then records added to the DataGridView don't get added to my EntitySet (though bizarrely any updates ARE reflected).

• Attempt Four

As per #3, but now I handle the AddingNew event for the BindingSource and explicitly create my object and add it to the EntitySet. This then works EXCEPT that if the user enters the new entry row at the bottom of the grid, I get a row full of default values added to my database. If the user keeps entering the row, I get a whole lot of rows. So that's no good either.

There must be a way to do this - surely to display some database records in a grid and allow full editing is the most basic of requirements (and it's not much use having the rows displayed in a random order)?

Any help most gratefully received...

Answer 1

I would still welcome any advice on a better way of doing this, but the following seems to be working for me.

I create a deriviative of BindingList to wrap my LINQ-returned records, and then pass that the ObjectContext and EntityCollection. It overrides the AddNewCore and CancelNew to put objects into the database as well as the BindingList and seems to persist changes without the unwanted blank rows if you move focus into the bottom row but don't write anything.

class LinqEntityBinding<T> : System.ComponentModel.BindingList<T> 
    where T : System.Data.Objects.DataClasses.EntityObject
{
    public LinqEntityBinding(
        System.Data.Objects.ObjectContext context,
        System.Data.Objects.DataClasses.EntityCollection<T> collection,
        IList<T> list)
        : base(list)
    {
        _context = context;
        _collection = collection;
    }
    System.Data.Objects.ObjectContext _context;
    System.Data.Objects.DataClasses.EntityCollection<T> _collection;

    protected override object AddNewCore()
    {
        object obj = base.AddNewCore();
        _collection.Add((T)obj);
        return obj;
    }

    public override void CancelNew(int itemIndex)
    {
        object obj = Items[itemIndex];
        _collection.Remove((T)obj);
        if (((T)obj).EntityState != System.Data.EntityState.Detached)
        {
            _context.DeleteObject(obj);
        }
        base.CancelNew(itemIndex);
    }
}

Then instead of:

bindingSource.DataSource = customer.Widgets;  // unsorted

I have:

bindingSource.DataSource = 
    new LinqEntityBinding<Widget>(db, customer.Widgets, 
    customer.Widgets.OrderBy(w => w.widgetness).ToList()); // sorted