Entity Framework 6.4: Third-level table linking is not tracked correctly in context when used with a binding source

Question

I have three tables. The first table is called Order, the second OrderPosition and the third Item. OrderPosition is linked to Order and Item to OderPosition. Order contains many OrderPosition and OrderPosition contains many Item.

I use Entity Framework 6.4.

If I want all the related items in table Item for a specific entry in table OrderPosition, this code works:

Dim OrderPosition = Await Task.Run(Function() _context.OrderPosition _
                               .Include(Function(a) a.Item) _
                               .FirstOrDefault(Function(a) a.IdOrderPosition = OrderPosition_ID))

Dim item As New BindingList(Of DataModel.Item)(OrderPosition.Item)
BindingSourceItems.DataSource = item 

I can change/delete/add entries to table item.

However, if I now want to have all the elements of table Item that are assigned to the end of table Order, then they will be displayed, but if I make changes (e.g. the BindingSource is bound to a datagrid), then the context does not notice this and therefore cannot be saved.

I tried this code:

Dim item = Await Task.Run(Function() _context.OrderPosition _
                               .Include(Function(a) a.Item) _
                               .Where(Function(a) a.FkIdOrder = Order_ID) _
                               .SelectMany(Function(a) a.Item) _
                               .ToList())

BindingSourceItems.DataSource = New BindingList(Of DataModel.Item)(item)

Now the question is whether this is even possible and if so, how would it work?

Answer 1

The second query (with SelectMany) seems more to the point because it queries Items directly. BTW, you can remove the Include, it is ignored.

As for the binding, I remember that DbSet.Local should be used for binding lists. First, the data should be loaded into the context:

Await _context.OrderPosition _
    .Where(Function(a) a.FkIdOrder = Order_ID) _
    .SelectMany(Function(a) a.Item) _
    .LoadAsync()

Then bind the Local collection:

BindingSourceItems.DataSource = _context.Items.Local.ToBindingList()