Data Binding WPF Lookup Combobox Values to EF Entities

It’s extremely common to have to hook up lookup tables on your data entry forms in order to populate foreign keys in a database. I’ve talked about how to do this in Winforms and WPF with Datasets and LINQ to SQL before:

• How Do I: Create a Lookup Combobox in WPF?
• How Do I: Create a Master-Detail Data Entry Form in WPF?
• How Do I: Create Lookup Lists? 
• Creating Lookup Lists with LINQ to SQL
• Related Data Binding and ComboBoxes with LINQ to SQL
• Tips on Related Data Binding and ComboBoxes

The common theme between all of these is that the data sources, either the LINQ to SQL classes or the DataTables that we bind to, uses a navigation path based on the foreign key and that foreign key is exposed as a property (or DataColumn). For instance if we have a Customer related to Orders we would have a CustomerID property on Orders.

DataSets (like databases) rely on this type of navigation. So when you want to find the parent Customer of an Order you have to know the relation. Using typed datasets helps you more but you still end up having to know the details of relationships and foreign keys of the DataSet. That’s why people who are familiar with databases are usually comfortable with working with DataSets.

LINQ to SQL classes also include navigation properties as direct object references and collections. So they have both the foreign key and the navigation properties. Customer will have a collection of Orders and Order will have a reference back to Customer but the classes also contain the CustomerID property. This isn’t “pure” I suppose but it does make data binding and subsequent saves back to the database pretty much a no brainer.

For instance say I want to hook up a lookup combobox using a LINQ to SQL class on an Order with a reference to Customer. I want to display a list of Customers the user can pick from and that Customer should be associated with that Order. It’s pretty straight forward and works the same with DataTables.

XAML:

 <Window.Resources>
    <CollectionViewSource x:Key="OrdersSource" />
    <CollectionViewSource x:Key="CustomerLookup" />
</Window.Resources>

 <Grid Grid.Row="1" Name="Grid1" DataContext="{Binding Source={StaticResource OrdersSource}}">
<ComboBox Height="23" Name="ComboBox1" Width="177" Margin="2"  HorizontalAlignment="Left" 
          IsEditable="False"
          ItemsSource="{Binding Source={StaticResource CustomerLookup}}"
          DisplayMemberPath="LastName"
          SelectedValuePath="CustomerID" 




          SelectedValue="{Binding Path=CustomerID}" 
          />
...

Code-behind:

 Class Window1
    Private db As New MyDataObjectContext
    Private OrderData As IEnumerable(Of Order)

    Private Sub Window1_Loaded(ByVal sender As Object, ByVal e As System.Windows.RoutedEventArgs) Handles Me.Loaded
        'Load all the orders from the database
        Me.OrderData = db.Orders
        'Get the customer lookup list (this is the Combobox ItemsSource)
        Dim customerList = From c In db.Customers _
                           Where c.Orders.Count > 0 _
                           Order By c.LastName, c.FirstName

        Dim ordersSource = CType(Me.FindResource("OrdersSource"), CollectionViewSource)
        ordersSource.Source = Me.OrderData
        Dim custSource = CType(Me.FindResource("CustomerLookup"), CollectionViewSource)
        custSource.Source = customerList.ToList()

    End Sub
...

Almost all of this code is just to set up the context of this discussion, you can watch this video for details on building a complete example. Here I’m using CollectionViewSources in the XAML and setting their Source property in code. This technique is handy especially if you are using nested DataTemplates. The important piece to note are the four properties on the Combobox. ItemsSource, DisplayMemberPath, SelectedValue, and SelectedValuePath. To set up your combobox:

1. Set the ItemsSource to the list of Customers you want to display in the Combobox. 
2. Next set the DisplayMemberPath to the property name on this list that you want to use to display in the list, here I used LastName.
3. Then set the SelectedValuePath to the property name on this list that will be used to populate the foreign key value on the Order.
4. Finally you set the SelectedValue to the property binding on the Order that is foreign key. LINQ to SQL (and DataSets) will happily save your data with this binding in place.

This is how you always bind DataTables and it also works well for LINQ to SQL classes. But since LINQ to SQL classes also include the navigation properties (the Orders collection on Customer and the Customer object reference on Order) you can use a different technique by binding directly to the Customer reference. This is the only choice we have with Entity Framework entities in .NET 3.5 SP1.

What’s unique with Entity Framework is that the associations between other entities use only navigation properties -- so there isn’t a CustomerID foreign key property value on the Order at all.

What you have to do instead is bind directly to the Customer object reference. The change in the above example is the Combobox binding in XAML:

 <ComboBox Height="23" Name="ComboBox1" Width="177" Margin="2"  HorizontalAlignment="Left" 
          IsEditable="False"
          ItemsSource="{Binding Source={StaticResource CustomerLookup}}"
          DisplayMemberPath="LastName"          
          SelectedItem="{Binding Path=Customer}" 
          />

The important pieces in this case are the three properties on the Combobox. ItemsSource, DisplayMemberPath, and SelectedItem.

1. Set the ItemsSource to the list of Customers you want to display in the Combobox -- same as before.
2. Next set the DisplayMemberPath to the property name on this list that you want to use to display in the list, here I used LastName – same as before.
3. Now set the SelectedItem to the property binding on the Order that is the navigation property to Customer.

This will work properly with LINQ to SQL classes and Entity Framework entities but there is one caveat. You need to make sure that you pull the entire Customer entity into in the lookup list from the same ObjectContext you used to query the Orders. This is because the same ObjectContext (DataContext in LINQ to SQL) needs to resolve the entity references between the queries. Entity Framework does this based on the EntityKeys. The neat side effect of this with EF is that you don’t have to pull down the Customers with the Orders query, they will automatically become references when the Customer lookup list is queried through the same context. (See my post on explicit load for more details on how to bring down related EF entities when you only make one call.)

This behavior may be desired in a lot of scenarios but if we do not need to modify the Customer, like in our example, this can be overkill especially if the Customer has a lot of large fields you aren’t using. In the first example we could have only pulled a subset of fields from the Customer table – the only required ones would be the ones used in the data binding, CustomerID and LastName. So with DataTables and LINQ to SQL classes that bind on the values we could have optimized our lookup list query to:

 Dim customerList = From c In db.Customers _
                   Where c.Orders.Count > 0 _
                   Order By c.LastName, c.FirstName _
                   Select c.CustomerID, c.LastName

This will not work if we bind directly to the navigation properties because the customerList is now a list of anonymous types and not a list of Customer entities. So binding to the values gives you greater flexibility with your lookup list queries. Unfortunately in the current version of EF you cannot bind this way but they are planning to enable this in the next version. In .NET 4.0 the EF team will add support for a new type of association called "FK Associations". Read more about that here.

And I’m working on the next set of WPF Forms over Data How Do I videos, this time with Entity Framework, that will hopefully explain how to use EF in practical way by building WPF data applications. Stay tuned!

Enjoy!

Comments

• Anonymous
  April 30, 2009
  PingBack from http://microsoft-sharepoint.simplynetdev.com/data-binding-wpf-lookup-combobox-values-to-ef-entities/

• Anonymous
  May 04, 2009
  Last week I wrote about how to data bind WPF lookup comboboxes to entities returned from the Entity Framework.

• Anonymous
  May 15, 2009
  I’ve been writing a lot about building WPF business applications with Entity Framework using Visual Studio

• Anonymous
  September 15, 2009
  Thanks Beth!!  I just wanted to tell you your blogs are a great source of guidance for me in learning WPF

• Anonymous
  October 22, 2009
  Thank you so much, Beth, for ItemsSource to be used with Entity Framework, I was getting mad to try to make working EF with SelectedValuePath and SelectedValue. And I can say that I've search unfortunately this in many blog before find the correct way to do in your very clear one. I put your blog in my favourites ! Merci beaucoup ! Didier, France

• Anonymous
  January 11, 2010
  Thank you for this great post. I now understand that I have to use the same Object Context to query lookup table with entity framework. But I have a problem. I have a page with the info on the orders, but the info on the customer is in a UserControl. So my ComboBox is in that control. I created a two collection view on my page "OrdersSource" and "CustomerLookup" but I don't know how to get "CustomerLookup" from my comboBox in my userControl. If I use ItemsSource="{Binding Source={StaticResource CustomerLookup}}" it doesn't find it. I can't use DynamicResource. And if I try with RelativeSource FindAncestor it says it can't find "PROPERTY" CustomerLookup. Any ideas how can I resolve this? Thank you!

• Anonymous
  January 14, 2010
  Please Beth - precisely the same data scenario as above - how do I make this work for a datagridcombobox where there is no SelectedItem Property? Thanks your help.

• Anonymous
  January 14, 2010
  Hi Mona -- you should be able to architect your user control so that you can pass the data into it instead of having the data access inside it. You should consolidate your data management in one place. Hi Pam -- You should be able to create a control template for the WPF data grid that contains the same type of lookup controls as shown above. http://msdn.microsoft.com/en-us/library/system.windows.controls.controltemplate(VS.100).aspx HTH, -B