LINQ - Databind to linked table?

Question

Hi all, You'll have to forgive me if I make a mistake here but I'm new here and have only been programming for 6 months whilst studying from home. I am writing a WPF application using Csharp and DLINQ (TO SQL). I've created my DBML file using the designer and the whole application runs fine with one snag I can't seem to solve. I have over 20 tables in the database, but only 3 are relevant here. Table 1 is called "Territories" which contains two fields: 1.TerritoryID (Primary Key) NON NULL 2.TerrritoryName Table 2 is called "Permissions" which contains 3 fields - This simulates amany-to-many relationship between territories and distributors: 1.PermissionID (Primary Key) NON NULL 2.TerritoryID (Foreign Key On Territories.TerritoryID) NULL 3.DistributorID (Foreign Key On Distributors.DistributorID) NULL Table 3 is called "Distributors" which contains 6 fields, one of which is important.. 1.DistributorID (Primary Key) NON NULL ------------------------- OK, I have a WPF window which contains the following (relevant only) code: public partial class WdwDistributorProperties : Window { private ceDataContext ce = null; private Distributor currentDist = null; private Int32 callingSelectedIndex; private BindingList distributorPermissions = null; private WdwDistributorProperties() { InitializeComponent(); } public WdwDistributorProperties(Distributor dist, Int32 currentSelectedIndex) { InitializeComponent(); ce = new ceDataContext("Integrated Security=true;Initial Catalog=ce;Data Source=localhost"); currentDist = ce.Distributors.Single(d => d.DistributorID.Equals(dist.DistributorID)); this.callingSelectedIndex = currentSelectedIndex; } private void Window_Loaded(object sender, RoutedEventArgs e) { this.Title += String.Format("{0} {1}", this.currentDist.FirstName, this.currentDist.LastName); IList permissionsList = ((IListSource)currentDist.Permissions).GetList(); distributorPermissions = permissionsList as BindingList; this.lvPermissions.DataContext = distributorPermissions; } ------The XAML for the relevant control is------ Territory Name -------------------------- This works fine for displaying the Permissions.TerritoryID but I actually want it to dispay the meaningful Territories.TerritoryName column from the linked table. I have created the option for a user to add new permissions which update the database correctly, and remove them - which only NULLs the Permissions.DistributorID column (not ideal but ce.SubmitChanges() will not delete the record - assuming because of the link between Permissions.TerritoryID and Territories.TerritoryID) so it will do for the moment. Question 1: How do I get the ListView (lvPermissions) to show the TerritoryName from the Territories table rather than the TerritoryID from the permissions table - but based on the relationship between them. I wish I could just write... Territory Name ...instead of the above XAML but I know you can't - what is a solution that will allow me show the TerritoryName whilst using the DBML automated code for updating the database? Question 2: Does anyone have a better solution to my Permissions table removal problem? The database has to be like it is (at least in my beginners opinion) because multiple distributors can have permissions to a territory and therefore a territory can have multiple distirbutors with permissions to it? Or Am I over complicating it? There is another part of the application which when the distributor logs in, they will be able to view territories based on their perissions - hence why I have linked the tables! Please help. Thanks in advance - Csharp Corner's newest member (probably).