Linq and WPF databinding with an association database

Question

I have 5 tables in my database with the following fields:

• Customer: ID (int), Name (string)
• Project: ID (int), Name (string)
• Task: ID (int), Name(string)
• Customer_Project: ID (int), CustomerID (int) ProjectID (int)
• Project_Task: ID (int), ProjectID(int), TaskID(int)

The last two tables create associations so any number of Customers can be associated with any number of Projects. Same with Projects and Tasks.

I'm trying to adhere to good MVVM standards. In my WPF control, I have already bound ListBoxes in my view to Customers, Tasks, and Projects in my ViewModel. I have a TwoWay SelectedCustomer bound property. In my model, I have objects for all of these elements and can reference them by ID, Name, etc.

What I want to do is create a list of SelectedProjects and SelectedTasks, where the user only selects a customer from the list, and then the two listboxes populate with the "associated" Projects according to Customer_Project, and likewise with Projects_Tasks.

I really don't want to hack this functionality in using a bunch of foreach loops, I figure there has got to be a slick way using Linq joins and binding to dynamic ObservableCollections.

Any ideas?

Answer 1

First of all, download the MVVM template for Visual Studio 2008. That will give you a ViewModelBase class that allows your ViewModels to inherit things like PropertyChange notification and so on. Next, do something like this:

View:

<StackPanel Orientation="Horizontal">
    <ListBox ItemsSource="{Binding Customers}" 
             SelectedItem="{Binding SelectedCustomer}" Width="100"/>
    <ListBox ItemsSource="{Binding Projects}" 
             SelectedItem="{Binding SelectedProject}" Width="100"/>
    <ListBox ItemsSource="{Binding Tasks}" Width="100"/>
</StackPanel>

ViewModel:

/// <summary>
/// MyViewModel class
/// </summary>
public class MyViewModel : ViewModelBase
{
    private DbDataContext _dc;

    /// <summary>
    /// Default constructor
    /// </summary>
    public MyViewModel()
    {
        _dc = new DbDataContext();

        Customers = new ObservableCollection<Customer>(
            (from c in _dc.Customers select c).ToList());
    }

    /// <summary>
    /// Customer List
    /// </summary>
    private ObservableCollection<Customer> _customers;
    public ObservableCollection<Customer> Customers
    {
        get { return _customers; }
        set
        {
            _customers = value;

            // Notify the UI that the collection has changed
            OnPropertyChanged("Customers");
        }
    }

    /// <summary>
    /// When the user selects a customer from the list, 
    /// populate the list of projects for the customer
    /// </summary>
    private Customer _selectedCustomer;
    public Customer SelectedCustomer
    {
        get { return _selectedCustomer; }
        set
        {
            _selectedCustomer = value;
            Projects = new ObservableCollection<Project>(
                (from p in _dc.Projects join c in _dc.Customer_Projects 
                 on p.ID equals c.ProjectID where c.CustomerID == SelectedCustomer.ID 
                 select p).ToList());
        }
    }

    /// <summary>
    /// When the user selects a project from the list, 
    /// populate the list of tasks for the project
    /// </summary>
    private Project _selectedProject;
    public Project SelectedProject
    {
        get {return _selectedProject;}
        set
        {
            _selectedProject = value;
            Tasks = new ObservableCollection<Task>(
                (from t in _dc.Tasks join p in _dc.Project_Tasks 
                 on t.ID equals p.TaskID where p.ProjectID == SelectedProject.ID 
                 select t).ToList());
        }
    }

    /// <summary>
    /// Project List
    /// </summary>
    private ObservableCollection<Project> _projects;
    public ObservableCollection<Project> Projects
    {
        get { return _projects; }
        set
        {
            _projects = value;

            // Notify the UI that the collection has changed
            OnPropertyChanged("Projects");
        }
    }

    /// <summary>
    /// Task List
    /// </summary>
    private ObservableCollection<Task> _tasks;
    public ObservableCollection<Task> Tasks
    {
        get { return _tasks; }
        set
        {
            _tasks = value;

            // Notify the UI that the collection has changed
            OnPropertyChanged("Tasks");
        }
    }
}