Q:

Issues with DataGrid binding and saving new rows to database

I'm working on a simple windows application using SQL Server Compact with Entity Framework 6, I have a TreeView which is populated by a database, there is also a DataGrid which should be filled based on the selected node from TreeView. It looks like this Image
Database Model:

Categories

CategoryID
CategoryName

SubCategories

SubID
SubName
CategoryID

Items

ItemID
ItemName
SubID

I used this code to fill the DataGrid, everything was working just fine, but it won't save new rows to the database, editing existed rows is working though.
private void treeView_AfterSelect(object sender, TreeViewEventArgs e)
{
    if (treeView.SelectedNode.Level == 1)
    {
        BindingSource bs = new BindingSource();
        bs.DataSource = (from a in context.Items where a.SubID == (int)treeView.SelectedNode.Tag select a).ToList();
        dataGrid.DataSource = bs;
    }
}  

So, I tried this. Saving new rows and editing existed ones worked, the thing is when selecting a node in TreeView it should only show the Items linked to the selected node, but it doesn't it adds the new query result to the old one, displaying both queries on the DataGrid, would be probably more if I selected more nodes.
private void treeView_AfterSelect(object sender, TreeViewEventArgs e)
{
    if (treeView.SelectedNode.Level == 1)
    {
        context.Items.Where(a => a.SubID == (int)treeView.SelectedNode.Tag).Load();
        dataGrid.DataSource = context.Items.Local.ToBindingList();
    }
} 

There is one more thing I'm trying to do, when selecting a Sub-Category from TreeView I should get Items linked to this particular Sub-Category displayed on DataGrid, so is it possible when adding a new row in DataGrid it automatically set the SubID field in Items to the ID of selected node, linking this newly added item to the selected Sub-Category in TreeView.
Any help would be appreciated, and sorry if the solution is obvious, but all of this is new to me. Thanks.

A:

As for the previously loaded items appearing when selecting another node, you should add this line in treeView_AfterSelect:
foreach(var item in context.Items.Local.ToList())
    context.Entry(item).State = System.Data.Entity.EntityState.Detached;

and then continue with
context.Items.Where(a => a.SubID == (int)treeView.SelectedNode.Tag).Load();

The added line removes the items from the change tracker and they will effectively be removed from the Local collection. The Local collection of a DbSet<T> contains all items that a context has ever loaded and have not been deleted. So with each Load statement you keep adding items to this collection. By changing their state to Detached the context no longer "knows" about their existence and they disappear from the Local collection.
Notice that you can't do
context.Items.Local.Clear();

because that will mark all items for delete, besides removing them from the Local collection. SaveChanges will delete the items from the database.

As for adding new items to a new subcategory, the best thing to do is add these items to an Items collection in SubCategory. Doing that, you don't need a foreign key value, which isn't know at that moment, but when calling SaveChanges, EF will save the SubCategory first and set the generated FK value in the Items "just in time".
It will look like
var subcat = new SubCategory();
var item = new Item();
subcat.Items.Add(item); // subcat.Items must have been initialized! 
context.SubCategories.Add(subcat); // Also marks item as Added
context.SaveChanges();

Now EF first saves subcat, reads its generated key value, sets item's foreign key value and saves item, all in one transaction.
If you just create a new item for an existing SubCategory you can set item.SubId directly. But also subcat.Items.Add(item) can still be used, if subcat is attached to the context.

