Binding Master/Detail to Master/Detail?-CodePudding

My question is how does one tie two master/detail sources together. I am working in C# winforms and using VS 2017. My situation is I have 4 tables (LETTINGS, LINK_CONTRACT_LETTING, CONTRACTS, DESES) from an Access Database, that I am bringing into a dataset, which has four tables and three table relationships. See Image. Tables and Relationships

The CONTRACTS and DESES tables are bound to the textboxes on the form and these textboxes change to the next or previous record properly. The binding source code for the CONTRACTS parent table is:

//Contracts Binding
bsContracts = new BindingSource();
bsContracts.DataSource = dsPlanning;                    //DataSet
bsContracts.DataMember = contracts.TableName;       //DataTable

The binding source code for the Deses child table is:

//Deses Binding
bsDeses = new BindingSource();              //Deses has a Details relationship to Contracts as in Master to Details. Therefore this binding is different.
bsDeses.DataSource = bsContracts;           //Master binding source bsContracts
bsDeses.DataMember = "ContToDes";       //"ContToDes" is the data relationship between the Contracts and Deses tables.

I also have done the same thing to the LETTINGS table and the LINK_CONTRACT_LETTING table master/detail like so:

//Lettings Binding
bsLettings = new BindingSource();
bsLettings.DataSource = dsPlanning;                     //DataSet
bsLettings.DataMember = lettings.TableName ;        //DataTable

//Links Binding
bsLinks = new BindingSource();
bsLinks.DataSource = bsLettings;        //Master binding source bsLettings
bsLinks.DataMember = "LetToLink";       //"ContToLink" is the data relationship between the Contracts and Link tables.

These additional 2 tables work together just like the first 2 tables.

I am not sure how to link the LINK_CONTRACT_LETTING to the CONTRACTS table so that when a letting is selected only the contracts and deses associated with that letting are shown? Is this possible or should I be doing something else? If I should be doing something else can you give me some direction?

I have tried changing the datasource and datamember for bsContracts and bsLinks, but so far nothing has worked. Can I have a master/detail tied into another master/detail?

Thank you for your help.

CodePudding user response：

Automatic filtering through binding only works from master to detail over a one-to-many relationship. When you have a third table linking two others in a many-to-many relationship, you have two masters and one detail. You cannot select a master record at one end and have that automatically filter the master records at the other end. You can automatically filter as far as the link table, but then you need to manually filter the other master. For instance, you can select a Letting record and have the BindingSource for the link table filter automatically. You then need to gather the IDs from that BindingSource and use them to filter the BindingSource for the Contract table, e.g.

var contIds = bsLinks.Cast<DataRowView>().Select(drv => drv.Row.Field<int>("ContId"));

bsContracts.Filter = $"ContId IN ({string.Join(", ", contIds)})";