Can't get DataGridView to refresh over Linq to SQL (WinForm)

Question

Very strange situation here: I'm using L2S to populate a DataGridView.
Code follows:

    private void RefreshUserGrid()
    {
    var UserQuery = from userRecord in this.DataContext.tblUsers
                    orderby userRecord.DisplayName
                    select userRecord;

    UsersGridView.DataSource = UserQuery;

//I have also tried
//this.UserBindingSource.DataSource = UserQuery;
//UsersGridView.Datasource = UserBindingSource;

    UsersGridView.Columns[0].Visible = false;
    }

Whenever I use L2S to Add/Delete records from the database, the GridView refreshes perfectly well.
However, if someone is editing the grid and makes a mistake, I want them to be able to hit a refresh button and have their mistakes erased by reloading from the datasource.
For the life of me, I can't get it to work.

The code I am currently using on my refresh button is this:

private void button1_Click(object sender, EventArgs e)
{
    this.DataContext.Refresh(RefreshMode.OverwriteCurrentValues);
    RefreshUserGrid();
}

But the damn GridView remains unaffected. All that happens is the selected row becomes unselected.

I have tried .Refresh(), .Invalidate(), I've tried changing the DataSource to NULL and back again (all suggestions from similar posts here)....none of it works. The only time the Grid refreshes is if I restart the app.

I must be missing something fundamental, but I'm totally stumped and so are my colleagues.
Any ideas?

Thanks!

Answer 1

The easiest would be to use a BindingSource. Create an instance of the BindingSource to the class initialize it to the data query then assign the BindingSource to the UsersGridView.

The BindingSource will handle the updates etc. There are several events that can be caught for custom management.

This link gives an example of using a BindingSource

EDIT: My first post assumed Webforms instead of WinForms.

Answer 2

It seems that this is a bug in LINQ to SQL. I understand from the Janus GridEx folks that the cause of the problem is that LINQ presents a static list to the grid that is not refreshed after Refresh is called on the DataContext.

A solution is to simply re-instantiate the DataContext.