How to filter data using Entity Framework in a way that DataGridView be editable and context track changes?

Question

I'm using C# Windows Form Application to populate data from sql server database table using Entity Framework (EFWinForms) using the following code :

MyEntityDataModel db = new MyEntityDataModel();
MyEDS = new EntityDataSource();
MyEDS.DbContext = db;
MyDataGridView.DataSource = MyEDS;
MyDataGridView.DataMember = "MyTable";

It works fine. When user edit,add data ; data can be saved using the following code :

MyEDS.SaveChanges();

I want a way to filter these data throug Entity Data source so that MyDataGridView remains editable and any update done by user in filtered data can still be Saved back to database. Note: When using linq to entity to filter data it works great but it just populate a snapshot of data that can't be edited or updated by user again.

Answer 1

There are some important points which you should consider when you want to work with entity framework in windows forms in connected mode if you want to keep the DataGridView editable even when you have applied a filter.

Use a single instance of your DbContext

Use a single instance of your DbContext. If you create a new instance when saving changes, the new instance can't see any change that you made on other instance. So declare it at form level:

TestDBEntities db = new TestDBEntities();

Load Data - Bind To Local Storage of Entities

When you work with Entities in connected mode, load data using Load method of db set like db.Products.Load() or by calling ToList like db.Products.ToList().

Bind your BindingSource to db.Products.Local.ToBindingList(). So if you add or remove items to/from binding source, change tracker detects changes and adds and removes items for you.

To see ToBindingList extension method add using System.Data.Entity;.

If adding is enabled in your DataGridView, then turn off proxy creation to prevent exceptions when filtering.

db.Configuration.ProxyCreationEnabled = false;
db.Products.Load(); 
this.productsBindingSource.DataSource = db.Products.Local.ToBindingList();

Filter Data Using Linq

To filter data, use linq. You can not use Filter property of BindingSource when the underlying list is BindingList<T>; Only underlying lists that implement the IBindingListView interface support filtering.

To apply filtering use linq. For example:

var filteredData = db.Products.Local.ToBindingList()
    .Where(x => x.Name.Contains(this.FilterTextBox.Text));
this.productsBindingSource.DataSource = filteredData.Count() > 0 ?
    filteredData : filteredData.ToArray();

Remove Filter

To remove filter, just set the data source of your binding source to the local storage of your entities again. This way adding and removing will work when you remove filter.

this.productsBindingSource.DataSource = db.Products.Local.ToBindingList();

Add/Remove/Edit

Add will work only in unfiltered mode. To let the user add entities, remove filter.

Editing will work in both filtered or unfiltered mode.

Remove works in both filtered or unfiltered mode. But if you use BindingNavigator in filtered mode, you can't rely on its delete button. To make it working for both filtered mode and non-filtered mode should set DeleteItem property of BindingNavigator to None and handle its delete item click event and write your own code:

if (productsBindingSource.Current != null)
{
    var current = (Product)this.productsBindingSource.Current;
    this.productsBindingSource.RemoveCurrent();
    if (!string.IsNullOrEmpty(this.FilterTextBox.Text))
        db.Products.Local.Remove(current);
}

Dispose DbContext on disposal or close of your Form

For a real world application consider disposing the DbContext on disposal or close of form:

db.Dispose();

Sample Code

Below is a sample code which contains what I described above.

using System.Data.Entity;

SampleDbEntities db = new SampleDbEntities();
private void Form1_Load(object sender, EventArgs e)
{
    db.Configuration.ProxyCreationEnabled = false;
    db.Products.Load();
    this.productsBindingSource.DataSource = db.Products.Local.ToBindingList();
}
private void FilterButton_Click(object sender, EventArgs e)
{
    if (string.IsNullOrEmpty(this.FilterTextBox.Text))
    {
        this.productsBindingSource.DataSource = db.Products.Local.ToBindingList();
    }
    else
    {
        var filteredData = db.Products.Local.ToBindingList()
            .Where(x => x.Name.Contains(this.FilterTextBox.Text));
        this.productsBindingSource.DataSource = filteredData.Count() > 0 ?
            filteredData : filteredData.ToArray();
    }
}
private void productBindingNavigatorSaveItem_Click(object sender, EventArgs e)
{
    this.Validate();
    productsBindingSource.EndEdit();
    db.SaveChanges();
}
private void bindingNavigatorDeleteItem_Click(object sender, EventArgs e)
{
    if (productsBindingSource.Current != null)
    {
        var current = (Product)this.productsBindingSource.Current;
        this.productsBindingSource.RemoveCurrent();
        if (!string.IsNullOrEmpty(this.FilterTextBox.Text))
            db.Products.Local.Remove(current);
    }
}