Keep DbContext open for DataGridView

Question

I have a DataGridView and the DataGridView's DataSource is a BindingList I got from the Entity Framework (V6) via context.Person.Local.ToBindingList(). After I set the DataSource to this BindingList, I dispose the context, because I read that keeping the context open would be bad practice.

So, if I wanted to add a new row, I would click on the "add" button that comes with the BindingNavigator that got created when I dragged the "people" object data source to my Windows Form. Every time I click the "add" button, I get an exception that tells me that the context has been disposed. Do I need to keep the context open all the time when using DataGridView? Oh and: the DataSource might change during runtime depending on the selection of a ListBox Item.

Also, when the context has been disposed and I edited one row from the DataGridView, how could I find out (after multiple changes) which row has changed? I tried to do:

foreach(DataGridViewRow row in peopleDataGridView.Rows)
{
    People item = (People)row.DataBoundItem;
    if (item != null)
    {
        db.People.Attach(item);
    }
}
db.SaveChanges();

...but SaveChanges() did not recognize any changes. However, if I force every attached item to a "modified" state, it works. But I do not want to change 100 items to "modified", if only one got actually modified.

Any ideas?

EDIT 1 Oh well, so I changed my code to keep the context open all the time (or at least as long as the form gets displayed). Now, I ran into a different problem (people may have many jobs):

private void listBox1_SelectedValueChanged(object sender, EventArgs e)
{
    People p = (People)listBox1.SelectedItem;
    if(p != null)
    {
        //jobBindingSource.Clear(); this caused another error at runtime...
        db.Entry(p).Collection(b => b.Job).Load();
        jobBindingSource.DataSource = db.Job.Local.ToBindingList();
    }
}

The DataGridView that is bound to this jobBindingSource instance shows the correct jobs for a person, but in addition to the jobs from the previously selected person. I tried to Clear() the entries, but if I do this and click on the same person twice, the datagridview starts to sometimes show no entries at all. A strange behaviour. What am I doing wrong now?

EDIT 2 Okay... I found a solution myself. But I refuse to accept that this is the correct way to do it:

private void listBox1_SelectedValueChanged(object sender, EventArgs e)
{
    People p = (People)listBox1.SelectedItem;
    if(p != null)
    {
        db.Dispose();
        db = new PeopleJobsEntities();
        db.People.Attach(p);
        db.Entry(p).Collection(person => person.Job).Load();
        jobBindingSource.DataSource = db.Job.Local.ToBindingList();
    }
}

Only if I dispose the context and open it anew, the whole thing works. The reason is that if I clear the local cache (of db.Job.Local), its entries will not be reloaded again even if I use the Load() method. Is there some way to force the reloading of entities?

Answer 1

While I try not to keep the DBContext open for a long period of time, with datagrids you don't have much choice. I set my grid's DataSource property to IQueryable<T> and then all the edits, deletes and additions are taken care of by the grid and context itself. You just have to call dbContext.SubmitChanges() whenever you want to persist the changes. You can save each time a user leaves a row by saving on the RowLeave or the RowValidated event. Or you can save when you close the form. But also make sure you call dbContext.Dispose() when you close the form as well.

To find out which rows change you can view the ChangeSet that is returned by doing the following:

var changes = dbContext.GetChangeSet();
dbContext.SubmitChanges();