WPF, optional child relationships, Foreign Keys and ComboBox

I was recently coding a data entry screen for a WPF application using a type data set.

The typed dataset had a data table that looked like:

• Entity: Order
  • Id
  • Name
  • ParentOrderId: Optional, nullable, no default “id” assigned to it

It is a recursive relationship. The data column allows nulls because it is an optional relationship and no default values are set on the field. In the WPF data entry screen I needed to have a combobox that allows a user to set the parent order. I am also using a MVVM model to bind to the data context of the toplevel UserControl.

The question is, how do I setup a combox box to handle potential nulls on the FK?

Here’s some XAML:

UserControl ... Name="TheForm">

<!-- The master part of the form displays a datagrid of orders -->

<!-- The detailed part of the form below has the selected item of the datagrid as its data context -->

  <ComboBox ItemsSource="{Binding Path=DataContext.Orders, ElementName=TheForm}"
    DisplayMemberPath="Name" SelectedValuePath="ParentOrderId"
    SelectedValue="{Binding ParentOrderId}" .. />

</UserControl>

But this did not work so well. Why? Well the ParentOrderId column can be null and there is no default integer value assigned to it. So naturually, my binding did not work so well with it. What’s missing? Well the combox box, if it does not find a value selected value, displays nothing by default. That’s fine. We really need to handle the nulls. We can do this by just adding a value converter to the Selected Value binding using a negative integer as the value when the ParentOrderId column is null. In this case, it can be a value of type DBNull as returned from ADO.NET.

We do need to remember that the SelectedValuePath represent the path on the ItemSource data that you want to “select” as the value that will be placed into the SelectedValue ComboBox property. In this case we want the SelectedValue to place the value identified by SelectedValuePath into the ParentOrderId of the “record.” So we see that our selecting the ParentOrderId from the “child” record is actually wrong. We really want the ID of the order record.

The record is the default data context of the ComboBox. The MVVM is the data context for the UserControl itself. That’s why the ItemsSource uses ElementName to reference the form so we can get back the main view model. The view model has a property called Orders on it that holds all of the orders. You can’t see where the ComboBox’s DataContext is being set because I snipped the code.

So we need to write a converter and fix the binding specification for SelectedValuePath. First lets write a value converter. We know that only positive values are valid ID on the order record. So we’ll use –1 as an invalid ID for records with ParentOrderId set to null. That way, we know that we’ll never have an overlap with our dummy id used for the UI and any valid order Ids in the database. Note that if we were using an entity model such as with EF or NHibernate, this is not an issue because the int ParentOrderId would never be seen. It would be a null Order object which is easier to bind to without problems.

Here’s the uconverter:

/// <summary>
    /// Convert a null value in the database into a dummy FK id. Convert it back
    /// again to a null when data binding back to the source. You don't need
    /// this type of converter when using an ORM. You need this with optional
    /// relationships when using ADO.NET and data tables/sets.
    /// </summary>
    public class FKConverter : IValueConverter
    {
        #region IValueConverter Members

        public object Convert(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
        {
            if (value == null)
                return -1;
            if (value is DBNull || value == DBNull.Value)
            {
                return -1;
            }
            return value;
        }

        public object ConvertBack(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
        {
            if (value == null)
            {
                return DBNull.Value;
            }
            else if (value is Int32)
            {
                if ((int)value == -1)
                    return DBNull.Value;
            }
            return value;
        }

        #endregion
    }

We also just need to change the binding specification to:

<ComboBox 
...
SelectedValue="{Binding ParentDomainId, Converter={StaticResource fkc}}"
SelectedValuePath="ID"

/>

That takes care of the issue nicely.