Displaying and editing many-to-many relational data in a WPF DataGrid

Posted: October 31, 2013 | Author: Magnus Montin | Filed under: WPF | Tags: Entity Framework, MVVM, WPF, XAML |4 Comments

This post provides an example of how you could display and let the user edit many-to-many relational data from Entity Framwork in a dynamic and data-bound DataGrid control in WPF by programmatically adding a DataGridCheckBoxColumn to the grid for every entity object that represents a row in the “child” table of the relationship between the two tables:

Many-to-many

A many-to-many relationship is one where two data tables or entities have multiple rows that are connected to one or more rows in the other table.

In this particular example, one user can belong to multiple groups and one group can have multiple users. Another typical example of this kind of relationship is when you have a table representing students and another one representing courses and a student can take several courses simultaneously, while a course can be attended by several students at a time.

In a relational database, a join table is generally used to create the relationships:

/* define the tables */
CREATE TABLE [User]
(
	UserId INT NOT NULL IDENTITY (1, 1) PRIMARY KEY,
	Username NVARCHAR(10) NOT NULL UNIQUE,
	Firstname NVARCHAR(20) NOT NULL, 
	Lastname NVARCHAR(20) NOT NULL
)

CREATE TABLE [Group]
(
	GroupId INT NOT NULL IDENTITY (1, 1) PRIMARY KEY,
	GroupName NVARCHAR(20) NOT NULL UNIQUE
)

CREATE TABLE [UserGroups]
(
	UserId INT NOT NULL, 
	GroupId INT NOT NULL, 
	PRIMARY KEY (UserId, GroupId),
	FOREIGN KEY (UserId) REFERENCES [User] (UserId),
	FOREIGN KEY (GroupId) REFERENCES [Group] (GroupId)
)

/* insert some sample data */
INSERT INTO [User] (Username, Firstname, Lastname) VALUES ('magmo', 'Magnus', 'Montin')
INSERT INTO [User] (Username, Firstname, Lastname) VALUES ('johndo', 'John', 'Doe')
INSERT INTO [User] (Username, Firstname, Lastname) VALUES ('janedo', 'Jane', 'Doe')

INSERT INTO [Group] (GroupName) VALUES ('Administrators')
INSERT INTO [Group] (GroupName) VALUES ('Publishers')
INSERT INTO [Group] (GroupName) VALUES ('Readers')

INSERT INTO UserGroups (UserId, GroupId)
SELECT u.UserId, g.GroupId
FROM [User] u
CROSS JOIN [Group] g
WHERE u.Username ='magmo'

INSERT INTO UserGroups (UserId, GroupId)
SELECT u.UserId, g.GroupId
FROM [User] u
CROSS JOIN [Group] g
WHERE u.Username ='johndo'
AND (g.GroupName = 'Publishers' OR g.GroupName = 'Readers')

INSERT INTO UserGroups (UserId, GroupId)
SELECT u.UserId, g.GroupId
FROM [User] u
CROSS JOIN [Group] g
WHERE u.Username ='janedo'
AND g.GroupName = 'Readers'

Provided that the join table contains only the keys to the two related tables and no additional columns, the Entity Data Model Wizard in Visual Studio 2012 will by default create the below two entity classes when you add a new ADO.NET Entity Data Model and import all three tables above from an already existing database:

public partial class User
{
    public User()
    {
        this.Groups = new HashSet<Group>();
    }

    public int UserId { get; set; }
    public string Username { get; set; }
    public string Firstname { get; set; }
    public string Lastname { get; set; }

    public virtual ICollection<Group> Groups { get; set; }
}

public partial class Group
{
    public Group()
    {
        this.Users = new HashSet<User>();
    }

    public int GroupId { get; set; }
    public string GroupName { get; set; }

    public virtual ICollection<User> Users { get; set; }
}

Note that both generated entity classes have navigation properties of type System.Collections.Generic.ICollection<T> to get to their related data. As the classes have the partial modifier you can extend them with any additional properties that are not mapped against the database. For example, you might want to be able to get the full name of a user:

public partial class User
{
    public string FullName
    {
        get
        {
            return string.Format("{0} {1}",
                this.Firstname, this.Lastname);
        }
    }
}

Displaying data

Provided that you want to display a row per user and a column per group like in the image above, the DataGrid in the view should bind to a collection of User objects of the view model. Besides this collection, the view model also needs to expose a collection of all Group objects:

public class ViewModel : IDisposable
{
    private readonly Entities _context = new Entities();
    public ViewModel()
    {
        this.Groups = _context.Groups.ToList();
        this.Users = _context.Users.ToList();
    }

    #region Properties
    public List<Group> Groups
    {
        get;
        private set;
    }

    public List<User> Users
    {
        get;
        private set;
    }
    #endregion

    public void Dispose()
    {
        if (_context != null)
            _context.Dispose();
    }
}

The view can then iterate through this collection of Group objects and add a System.Windows.Controls.DataGridCheckBoxColumn for each one. The value of the property specified by the Path property of the Binding property of the DataGridCheckBoxColumn determines whether the generated CheckBox control will be checked or unchecked.

You typically use the Binding property of a DataGridCheckBoxColumn to bind to a source property of type System.Boolean (bool). However, you can also bind to a source property of some other type and use a value converter to convert the value of this type to a Boolean value that can be set on the CheckBox’s IsChecked property and this is exactly what you want to do here.

Remember that the DataGrid control will be bound to the collection of User objects of the view model. This means that the source object of the binding will be the User object and the User class doesn’t have any Boolean properties. What you want to do here is to bind to the Groups collection property of the User object and then have the converter return true or false depending on whether the User object’s Group collection contains the group that represents the current column. This means that you also need to pass the Group object to the converter and you can use the ConverterParameter property of the System.Windows.Data.Binding object that is set as the value for the Binding property of the DataGridCheckBoxColumn for this:

internal class GroupsToBooleanConverter : IValueConverter
{
    public object Convert(object value, Type targetType, object parameter, CultureInfo culture)
    {
        ICollection<Group> groups = value as ICollection<Group>;
        if (groups != null)
        {
            Group group = parameter as Group;
            if (group != null)
                return groups.Contains(group);
        }
        return false;
    }

    public object ConvertBack(object value, Type targetType, object parameter, CultureInfo culture)
    {
        throw new NotSupportedException();
    }
}

public partial class MainWindow : Window
{
    private readonly ViewModel _viewModel = new ViewModel();
    public MainWindow()
    {
        InitializeComponent();
        this.DataContext = _viewModel;
        CreateDataGrid();
    }

    private void CreateDataGrid()
    {
        this.dataGrid.AutoGenerateColumns = false;
        this.dataGrid.CanUserAddRows = false;

        /* Add a column for the displaying the full name of the user */
        this.dataGrid.Columns.Add(new DataGridTextColumn()
        {
            Header = "User",
            Binding = new Binding("FullName")
            {
                Mode = BindingMode.OneWay
            }
        });

        /* Add a column for each group */
        foreach (Group group in _viewModel.Groups)
        {
            DataGridCheckBoxColumn chkBoxColumn = new DataGridCheckBoxColumn();
            chkBoxColumn.Header = group.GroupName;

            Binding binding = new Binding("Groups");
            GroupsToBooleanConverter converter = new GroupsToBooleanConverter();
            binding.Converter = converter;
            binding.ConverterParameter = group;
            binding.Mode = BindingMode.OneWay;
            chkBoxColumn.Binding = binding;

            this.dataGrid.Columns.Add(chkBoxColumn);
        }

        /* Bind the ItemsSource property of the DataGrid to the Users collection */
        this.dataGrid.SetBinding(DataGrid.ItemsSourceProperty, "Users");
    }

    protected override void OnClosing(System.ComponentModel.CancelEventArgs e)
    {
        base.OnClosing(e);
        if (_viewModel != null)
            _viewModel.Dispose();
    }
}

<Window x:Class="Mm.DynamicDataGrid.Wpf.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:local="clr-namespace:Mm.DynamicDataGrid.Wpf"
        Title="MainWindow" Height="350" Width="525">
    <StackPanel Margin="10">
        <DataGrid x:Name="dataGrid"/>
    </StackPanel>
</Window>

Note that the XAML markup for the view contains only an empty DataGrid and the columns and bindings are defined in code. This is perfectly acceptable and does not break the MVVM pattern since all code is view related. The MVVM design pattern is about separating application logic from your view logic and not necessarily about eliminating code out of the views. You may be used to define the columns and bindings of a DataGrid in pure XAML but if you look at the code for the MainWindow class above, you should notice that it just creates the columns and binds to source properties of the view model just like you would do in XAML. The reason why this is done in C# code instead of static XAML markup is of course that the number of groups (columns) is dynamic and may vary over time. For example, if you add another row to the Group database table, you want another column representing this new group to show up in the DataGrid without any changes to the code.

Editing data

As the Mode property of the Binding objects that are being set as the values for the Binding properties of the DataGridCheckBoxColumn objects created in the foreach loop in the above code is set to System.Windows.Data.BindingMode.OneWay, the user won’t be able to check or uncheck the CheckBox controls in the DataGrid. The DataGrid will be read-only. If you require the ability to change the relationships between the entities, you somehow need to add or remove groups from the Groups collection property of the corresponding User object when the user checks or unchecks a CheckBox.

There are different ways of solving this. You could use a TwoWay binding (by setting the Mode property of the Binding object for each column to BindingMode.TwoWay) and implement the ConvertBack method of the GroupsToBooleanConverter class to modify the Groups collection of the bound User object. However, this method will only receive the Boolean value (value argument) of the IsChecked property of the CheckBox and the Group object (parameter argument). It doesn’t have any access to the source property, i.e. the User object. You could make the GroupsToBooleanConverter class derive from the System.Windows.DependencyObject class, add a dependency property to it and bind the source value to this dependency property. Note however that as a converter is not part of the visual tree, it doesn’t have any DataContext and this means that you have to get or inherit this from somewhere. You can read this external article published on the CodeProject for some ways of doing this if you decide to take this approach.

Another option is to bind the Command property of each CheckBox control to an ICommand property of the view model and let this command take care of adding and removing Group objects from the User object’s Groups collection property.

A DelegateCommand is common implementation of the System.Windows.Input.ICommand interface in WPF applications that uses the MVVM pattern. It invokes delegates when executing and querying executable status. There is no DelegateCommand class available in WPF out-of-the-box but there is one included in Prism, the framework and guidance for building WPF and Silverlight applications from the Microsoft Patterns and Practices Team. If you are not using Prism, you can implement your own one:

public class DelegateCommand : ICommand
{
    private readonly Predicate<object> _canExecute;
    private readonly Action<object> _execute;

    public DelegateCommand(Action<object> execute)
        : this(execute, null)
    {
    }

    public DelegateCommand(Action<object> execute, Predicate<object> canExecute)
    {
        _execute = execute;
        _canExecute = canExecute;
    }

    public bool CanExecute(object parameter)
    {
        if (_canExecute == null)
            return true;

        return _canExecute(parameter);
    }

    public void Execute(object parameter)
    {
        _execute(parameter);
    }

    public event EventHandler CanExecuteChanged
    {
        add
        {
            CommandManager.RequerySuggested += value;
        }
        remove
        {
            CommandManager.RequerySuggested -= value;
        }
    }
}

public class ViewModel : IDisposable
{
    ...
    private readonly DelegateCommand _addOrRemoveGroupCommand;
    public ViewModel()
    {
        _addOrRemoveGroupCommand = new DelegateCommand(AddOrRemoveGroup);
        ...
    }

    #region Properties
    ...
    #endregion

    #region Commands
    public DelegateCommand AddOrRemoveGroupCommand
    {
        get { return _addOrRemoveGroupCommand; }
    }

    private void AddOrRemoveGroup(object parameter)
    {
        //TODO: Implement logic
    }
    #endregion
    ...
}

To be able to access the CheckBox control that is generated by a DataGridCheckBoxColumn, you can create your own class that extends the DataGridCheckBoxColumn class and overrides the GenerateElement method. This method is responsible for creating the actual CheckBox control that is bound to the column’s Binding property.

In this method you could then bind the Command property of the created CheckBox control to the DelegateCommand of the view model. In the code below, a System.Windows.Data.RelativeSource object is used to describe the location of the binding source (the window) relative to the position of the binding target (the CheckBox).

internal class GroupDataGridCheckBoxColumn : DataGridCheckBoxColumn
{
    protected override FrameworkElement GenerateElement(DataGridCell cell, object dataItem)
    {
        CheckBox checkBox = base.GenerateElement(cell, dataItem) as CheckBox;
        checkBox.IsHitTestVisible = true;

        /* Set Command binding */
        Binding commandBinding = new Binding("DataContext.AddOrRemoveGroupCommand");
        commandBinding.RelativeSource = new RelativeSource(RelativeSourceMode.FindAncestor, typeof(Window), 1);
        checkBox.SetBinding(CheckBox.CommandProperty, commandBinding);

        //TODO: Set CommandParameter

        return checkBox;
    }
}

Also note that you must set the IsHitTestVisible property of the returned CheckBox control to true in order to enable it as the base class’ (DataGridCheckBoxColumn) implementation of the GenerateElement method will return a read-only CheckBox control. By default, a user must double-click a cell in a DataGrid to enter the edit mode and when this happens for a DataGridCheckBoxColumn its GenerateEditingElement method is called to create an enabled CheckBox control. However, in this case the column will actually always be in read-only mode as the Mode property of Binding object set as the value for the Binding property is set to BindingMode.OneWay and the GenerateEditingElement method will never be called. By making the “read-only” CheckBox enabled by simply changing the value of its IsHitTestVisible property, the user will be able to check and uncheck it without having to double-click the cell first.

For the command of the view model to know what to do when it gets executed, you have to pass it some parameters. It needs to know which Group object that is to be added or removed to or from which User object. When you want to pass a parameter to a command from a view you do so by using the CommandParameter property. Although the CheckBox control – like any other command aware controls – only has a single CommandParameter property, you can pass it multiple values by using a System.Windows.Data.MultiBinding with a converter class that implements the System.Windows.Data.IMultiValueConverter interface.

In the code below, the CommandParameter property of the CheckBox control created by the GenerateElement method of the custom GroupDataGridCheckBoxColumn class uses a MultiBinding object to bind to the value of the IsChecked property of the generated CheckBox control itself, the User object and the Group object. Note that the Group object is passed to the constructor of the GroupDataGridCheckBoxColumn class when the column is created in the view while the User object is the DataContext of the CheckBox control.

internal class GroupDataGridCheckBoxColumn : DataGridCheckBoxColumn
{
    private readonly Group _group;
    public GroupDataGridCheckBoxColumn(Group group)
        : base()
    {
        this._group = group;
    }

    protected override FrameworkElement GenerateElement(DataGridCell cell, object dataItem)
    {
        CheckBox checkBox = base.GenerateElement(cell, dataItem) as CheckBox;
        checkBox.IsHitTestVisible = true;

        /* Set Command binding */
        Binding commandBinding = new Binding("DataContext.AddOrRemoveGroupCommand");
        commandBinding.RelativeSource = new RelativeSource(RelativeSourceMode.FindAncestor, typeof(Window), 1);
        checkBox.SetBinding(CheckBox.CommandProperty, commandBinding);

        /* Set Command parameter */
        MultiBinding commandParameterBinding = new MultiBinding();
        commandParameterBinding.Converter = new CommandParameterMultiConverter();
        commandParameterBinding.Bindings.Add(new Binding("IsChecked") { RelativeSource = RelativeSource.Self });
        commandParameterBinding.Bindings.Add(new Binding(".")); //the user object
        commandParameterBinding.Bindings.Add(new Binding(".") { Source = this._group }); //the group object
        checkBox.SetBinding(CheckBox.CommandParameterProperty, commandParameterBinding);

        return checkBox;
    }
}

public partial class MainWindow : Window
{
    ...
    private void CreateDataGrid()
    {
        ...

        /* Add a column for each group */
        foreach (Group group in _viewModel.Groups)
        {
            GroupDataGridCheckBoxColumn chkBoxColumn =
                new GroupDataGridCheckBoxColumn(group);
            ...

            this.dataGrid.Columns.Add(chkBoxColumn);
        }
        ...
    }
    ...
}

The multi value converter simply returns a copy of the bound values as an array of objects that the view model can use to perform the logic of modifying the relationships between the objects by adding or removing the Group object from User object based on the value of the IsChecked property of the CheckBox, i.e. if it was checked or unchecked:

public class CommandParameterMultiConverter : IMultiValueConverter
{
    public object Convert(object[] values, Type targetType, object parameter, CultureInfo culture)
    {
        return values.Clone();
    }

    public object[] ConvertBack(object value, Type[] targetTypes, object parameter, CultureInfo culture)
    {
        throw new NotSupportedException();
    }
}

public class ViewModel : IDisposable
{
    ...
    private void AddOrRemoveGroup(object parameter)
    {
        object[] parameters = parameter as object[];
        if (parameters == null)
            throw new ArgumentNullException("parameters");
        if (!parameters.Length.Equals(3))
            throw new ArgumentException("Invalid number of arguments.", "parameters");
        if (!(parameters[0] is bool))
            throw new ArgumentException("First argument is invalid.", "parameters");

        User user = parameters[1] as User;
        if (user == null)
            throw new ArgumentException("Second argument is invalid.", "parameters");

        Group group = parameters[2] as Group;
        if (group == null)
            throw new ArgumentException("Third argument is invalid.", "parameters");

        bool isAdd = Convert.ToBoolean(parameters[0]);
        bool existsInCollecton = user.Groups.Contains(group);
        if (isAdd && !existsInCollecton)
            user.Groups.Add(group);
        else if (!isAdd && existsInCollecton)
            user.Groups.Remove(group);
    }
    ...
}

To be able to persist the changes back to the data storage, you could then add a Button control to the view and bind it to another command of the view model that invokes a delegate that simply calls the SaveChanges method of the Entity Framework context:

<Window x:Class="Mm.DynamicDataGrid.Wpf.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:local="clr-namespace:Mm.DynamicDataGrid.Wpf"
        Title="MainWindow" Height="350" Width="525">
    <StackPanel Margin="10">
        <DataGrid x:Name="dataGrid"/>
        <Button Content="Save" Margin="0 10 0 0" 
                Command="{Binding SaveCommand}" />
    </StackPanel>
</Window>

public class ViewModel : IDisposable
{
    private readonly Entities _context = new Entities();
    private readonly DelegateCommand _saveCommand;
    ...
    public ViewModel()
    {
        _saveCommand = new DelegateCommand(SaveChanges);
        ...
    }
    ...

    #region Commands
    ...
    public DelegateCommand SaveCommand
    {
        get { return _saveCommand; }
    }

    private void SaveChanges(object parameter)
    {
        _context.SaveChanges();
    }
    #endregion
    ...
}

If you want to be able to reuse the custom DataGridCheckBoxColumn for more than one type of entity, you could create a generic class with a type parameter that specifies the entity type for the columns and pass the command as a constructor argument:

public class CustomDataGridCheckBoxColumn<T> : DataGridCheckBoxColumn
{
    private readonly T _columnEntityObject;
    private readonly ICommand _addOrRemoveCommand;
    public CustomDataGridCheckBoxColumn(T columnEntityObject, ICommand addOrRemoveCommand)
        : base()
    {
        this._columnEntityObject = columnEntityObject;
        this._addOrRemoveCommand = addOrRemoveCommand;
    }

    protected override FrameworkElement GenerateElement(DataGridCell cell, object dataItem)
    {
        CheckBox checkBox = base.GenerateElement(cell, dataItem) as CheckBox;
        checkBox.IsHitTestVisible = true;
        checkBox.SetValue(CheckBox.CommandProperty, _addOrRemoveCommand);

        /* Set the CommandParameter binding */
        MultiBinding commandParameterBinding = new MultiBinding();
        commandParameterBinding.Converter = new CommandParameterMultiConverter();
        commandParameterBinding.Bindings.Add(new Binding("IsChecked") { RelativeSource = RelativeSource.Self 
        //the data bound object:
        commandParameterBinding.Bindings.Add(new Binding("."));
        //the column entity object:
        commandParameterBinding.Bindings.Add(new Binding(".") { Source = this._columnEntityObject });
        checkBox.SetBinding(CheckBox.CommandParameterProperty, commandParameterBinding);

        return checkBox;
    }
}

private void CreateDataGrid()
{
    ...
    foreach (Group group in _viewModel.Groups)
    {
        CustomDataGridCheckBoxColumn<Group> chkBoxColumn = 
            new CustomDataGridCheckBoxColumn<Group>(group, _viewModel.AddOrRemoveGroupCommand);
        ...

        this.dataGrid.Columns.Add(chkBoxColumn);
    }
    ...
}

---