How to bind wpf ComboBox to Linq to SQL Foreign Key Property

I have a Linq to SQL EntitySet with a Foreign Key Relationship between two tables. The tables in question are a Task table (called Issues) and a Departments Table. The foreign key is the department name (Which is guaranteed unique). I am running into an issue in that you cannot change a Linq to SQL FK field if the corresponding data is loaded.

The ViewModel (Not true MVVM, I am trying to practice the concepts while learning xaml, WPF and Linq-SQL)

public class StatusBoardViewModel : INotifyPropertyChanged
{
    OIConsoleDataContext db = new OIConsoleDataContext();

    private IQueryable<Issue> issues;
    public IQueryable<Issue> Issues
    {
        get { // Lazy load issues if they have not been instantiated yet
            if (issues == null) {
                QueryIssues();
            }
            return issues; 
        }
        set {
            if (issues != value) {
                issues = value;
                OnPropertyChanged("Issues");
            }
        }
    }

    private IQueryable<Department> departments;
    public IQueryable<Department> Departments
    {
        get {
            // Lazy load开发者_运维技巧 departments if they have not been instantiated yet
            if (departments == null) {
                QueryDepartments();
            }
            return departments;
        }
        set {
            if (departments != value) {
                departments = value;
                OnPropertyChanged("Departments");
            }
        }
    }

    private void QueryDepartments()
    {
        Departments = from d in db.Departments
                      orderby d.DeptName
                      select d;
    }

    public void QueryIssues()
    {
        Issues = from i in db.Issues
                 where i.IssIsOpen == true
                 orderby i.IssDueDate
                 select i;
         // ....
    }

    #region INotifyPropertyChanged Members
}

My combo Box:

<ComboBox x:Name="DeptComboBox" 
  ItemsSource="{Binding Departments}" 
  DisplayMemberPath="DeptName"
  SelectedValuePath="DeptName"
  SelectedValue="{Binding Path=Issues/IssDepartment, Mode=TwoWay}"
  Grid.Column="2" Grid.ColumnSpan="2" Grid.Row="3" Margin="6,7,115,5"
  IsSynchronizedWithCurrentItem="True" /> 

As it stands it displays the combo box fine and defaults the selection to the correct department BUT if you make a change it throws an exception 'System.Data.Linq.ForeignKeyReferenceAlreadyHasValueException'

I tried adding

   SelectionChanged="DeptComboBox_SelectionChanged" 

to the combo box then using the following code behind to remove the department reference then add the new one. It seemed to work fine but I discovered that if the filter settings caused the linq query to be changed then the current item before the change gets its department changed to the value of the new default items department when the Selection_Changed event fires. I could not find a way to tell if the selection changed event came from the user rather than the binding property to prevent this. Besides that I was not happy with the use of code where it seems it should be in a binding or at least some kind of value converter.

// This was an attempt to work around that did not work
// The referneces are passed from the Window1 event handler
public void ChangeDepartment(Issue currentIssue, Department currentDept)
{
    if (currentIssue != null) {
        currentIssue.Department = null;
        currentIssue.Department = currentDept;
    }
}

I have to be honest with myself, I am in over my pay grade here but that's when you learn the most eh..

How should I handle this? I have read a dozen articles on the issue and found them to be conflicting and as clear as mud.

Thanks for all your help SO

Mike

Update I have had a very useful discussion with Chris Nicol below and he would probably already have the checkmark if I had a better handle on things. I think my biggest problem is that I am having to learn SQL, Linq, WPF and Database Design all at once. I have excellent books on all the subjects but they don't cover interoperation. For instance Pro Linq in C# 2008 is great... except it doesn't talk at all about Linq in WPF,

I have tried to do things as "MVVMish" as possible but, after a few false starts earlier, I decided it was one thing too many to learn at once. In practice I think I am close to MVVM except there is no commanding, I execute methods in the ViewModel class from the event handlers in the code behind. I am assuming that will make it fairly easy to refactor in the commanding structure later. My App is structured as follows:

The Model

• Linq to SQL dbml files
• partial classes for DataErrorInfo validation

The ViewModel

• The DataContext
• IQueryable collections for Tasks, Employees and Departments
• A selected task property that is used by the add/edit windows view
• Some properties for the view to bind to such as ShowDetailListItems() that triggers a different listbox ItemTemplate and is itself bound to a checkbox.
• Methods to perform querys
• Methods to open a window to add or edit a task and to submitChanges() upon return from said windows.

The View is bound to the viewmodel and the code behind contains:

• a property that holds an instance of the viewmodel class
• code to instantiate the viewmodel in the contructor and to set the datacontext to the viewModel
• The remainder of the code behind is event handlers that will be replaced by commands eventually

I have read Josh Smiths excellent article many times and explored the sample code. most of my format is based on that. The thread listed by Chris (Below) has a ton or new material for me to explore so I will spend some time digging into that and trying to determine how best to refactor things. This has gone far beyond some ComboBoxes not working, In fact part of the original issue was that the combo boxes were being bound to two different data contexts. Until I have a manageable architecture I think I am doing more harm that good trying to patch things up.

I will probably be back later with a checkmark for Chris

Grrr: "An attempt has been made to Attach or Add an entity that is not new, perhaps having been loaded from another DataContext..."

---

I would recommend separating your data access layer from your ViewModel and using an observable collection. Once you leverage MVVM better this sort of thing isn't that hard.

I suggest that you read Josh Smith's article on MVVM, it will give you a great insight to WPF and it's binding capabilities. Accomplishing what you're trying to do in WPF is quite easy once you understand how it's working.

Good luck!