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.

Solution

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

Comments

[EgyEast]

How could i use binding Source with Entity Framework in WinForms Application ??

[EgyEast]

Great answer. Works as charm exactly as i wanted. Manyyy thanks :)

[DWR C Sharper]

There's no point I denying it. I didn't read your question well enough. Sorry for the miss.

[EgyEast]

Could that way use Select to specific Columns like : this.productBindingSource.DataSource = db.Products.Local.Select(new {columns ....}).ToBindingList(); ?

[Reza Aghaei]

No. You can't edit non-entity models automatically. It's like when you write a select statement, for example SELECT c1, c2 FROM t1, then the result is not updatable and for update, you should map those columns manually to database columns. Here you should map those properties to your original entities.

[EgyEast]

Let us continue this discussion in chat.

[Sam Hobbs]

Did the chat session produce something useful for selecting specific columns? If there is an easy way to do that then it should be in an answer somewhere. I will continue to search and if I still find nothing then I will create a question.

[Reza Aghaei]

@user34660 No, As mentioned in above comments and in chat you can't edit non-entity models automatically. It's like when you write a select statement, for example SELECT c1, c2 FROM t1, then the result is not updatable and for update, you should map those columns manually to database columns. Here you should map those properties to your original entities.

[Reza Aghaei]

@user34660 What's your exact requirement?

[Sam Hobbs]

@Reza Aghaei, I have a table with foreign keys that I need but I don't want the foreign keys to show in the DataGridView. I have seen the suggestion to make columns hidden but instead I removed the relevant columns and that seems to work. Yeah, I understand we can't edit non-entity models automatically but it is a common requirement and it should be provided.

[Reza Aghaei]

@user34660 You can simply hide those columns in DataGridView. Also if you need, you can apply [Browable(false)] to those properties at design-time or run-time.

[Sam Hobbs]

@Reza Aghaei, I don't know how to use [Browable(false)] at design-time for an EF entity but I did not think of adding doing it during runtime and that seems useful.

[Reza Aghaei]

@user34660 So you may find this post useful: Is it possible to add an attribute to a property in a partial class?

[Reza Aghaei]

@user34660 Also this one or this one which are much harder, but really interesting. Being aware of such features opens new windows for you to do some dynamic tasks when needed.

[Henry]

Hi @RezaAghaei, I have tried Filter Data Using Linq on your answers and it filters the DataGridView +1 on this :), however, on my DataGridView I have a checkbox column which the user will check, but upon filter the checkbox is cleared, is there a way not to clear the checkbox? please advice if I have to post this as a separate question :)

[Reza Aghaei]

Hi @Henry, if I understand your question correctly, I guess this post will answer your question: DataGridView CheckBox column doesn't apply changes to the underlying bound object

[Henry]

Hi @RezaAghaei, I have tried your suggestion on the CommitEdit, but it still clears the checkbox. I guess its because the checkbox is not part of the item Entities, since the checkbox is just an additional checkbox on the DataGridView just for the user to be able to select their items. Is there other way to do this? Thanks very much in advance :)

[Reza Aghaei]

@Henry Surely if the field is not bound to your model, values of the field will be lost. To solve the problem, if you are using a model class, add a bool property to its partial class. Or if you are using a DataTable, add a bool column to the DataTable.

[Henry]

Hi @RezaAghaei, sorry for the late response, that solve the problem on the checkbox.. however I find it odd for my model class to have a property (isSelected) which is not totally related to the table (items). Maybe you could enlighten me on what good approach to take. But as for now since it fixed my problem I go with the solution.. Thanks very much brother :) :) :)

[Reza Aghaei]

Hi @Henry. You're welcome. You can add the non-mapped property to partial class of your model without adding it to database.

[Ahmed Suror]

GREAT!! it solved me many problems, except - when using filter - if the search string isn't exist in the Db my DataGridView acts strangly: LOOK: imgur.com/68VG0QH

[Ahmed Suror]

@RezaAghaei Why did you use db.Configuration.ProxyCreationEnabled = false; ?!!!

[Ahmed Suror]

@RezaAghaei Another last two Qs pease: 1- when filtering data if no data qualified for the filter then I get an empty DataGridView row like this image 2- When using your delete code: db.Products.Local.Remove((Product)this.productBindingSource.‌​Current); this.productBindingSource.RemoveCurrent(); It deletes two rows from the DataGridView and from the database, I had to only use db.Products.Local.Remove((Product)this.productBindingSource.‌​Current); Any suggestions?

[Ahmed Suror]

@RezaAghaei Of course look here I noticed that in filtered mode it delets one row, the problem occurs if outside filter mode

[Ahmed Suror]

@RezaAghaei Thanks very much, also please if you have response about Q 1

[Reza Aghaei]

@AhmedSuror Sure, I'll take a look at both issues. Thanks for the feedback!

[Reza Aghaei]

@AhmedSuror Fixed a few issues in the post: (1) Changed save code to do validation and end edit, then save. (2) Changed the filter code to prevent showing an empty row in case of not having search result. (3) Added the full code for delete which supports in both filtered and unfilered cases. (4) Added a short description about reason of disabling proxy generation.

[Ahmed Suror]

@RezaAghaei Thanks for your effort, and I've some notices: (1) Changed save code: Your code productsBindingSource.EndEdit(); I think it is not necessary beacuase db.SaveChanges(); works well, so am I wrong or missing something? (2) Changed the filter code: Your code this.productsBindingSource.DataSource = filteredData.Count() > 0 ? filteredData : filteredData.ToArray(); You used .ToArray in case that there is no rows exist, but I tried to make the BindingSource.Datasource always equals filteredData.ToArray(); and it worked so why did you use the condition? TO BE CONTINUED...

[Ahmed Suror]

@RezaAghaei (3) Added the full code for delete: I reviewed your delete code and before your reply I used this code and it worked and I want you to tell me is it the same? if (string.IsNullOrEmpty(txtSrch.Text)) { binSrc.RemoveCurrent(); } else { db.Products.Local.Remove((Product)binSrc.Current); binSrc.RemoveCurrent(); }

[Ahmed Suror]

@RezaAghaei Also I didn't use db.Configuration.ProxyCreationEnabled = false; and the app works well. Is there a problem

[Reza Aghaei]

@AhmedSuror Save: Calling Validate and EndEdit and helps you to raise validation events and also finish editing, so even if you are in middle of editing a cell, without leaving the cell, if click on save button, it will save. It's the correct way of saving. Filter: ToArray is just useful for the case that result is empty. If the search result is not empty, you should not call ToArray because if you do so, you cannot delete an item from the array, array is editable but not removable. Delete The code do the same, use the old one or the new one, whatever you like.

[Reza Aghaei]

@AhmedSuror ProxyCreation: If AllowUserToAddRow is enabled for DataGridView, you need to disable proxy creation. However if you don't experienced any problem, you can ignore it.

[Ahmed Suror]

@RezaAghaei Thanks for your patience, I'm new to EF and your answer and comments helped me tooooooo much really big thanks.. Theoretically, do you have an explanation why would .ToBindingList() produce an empty row while .ToArray() dosen't in filter mode?

[Reza Aghaei]

No worries @AhmedSuror - about ToArray, if you remember, in the first version I had assigned result of db.EntitySet.Local.Where() as data source. The thing is when the result is empty, not sure why, but the where enumerator contains a single item! But when it'd not empty, it works properly. I opted to use ToArray in case of an empty result, because it creates an empty array without any element and no one can add an element to an array.

[Ahmed Suror]

@RezaAghaei Something I noticed that might help in the empty row problem is that -in case of filtered data not exist- the DataGridView original database columns are hidden an replaced by one column named Current, and this image explains...

[Reza Aghaei]

That is the only member of where result enumerator.