Ioannis Panagopoulos blog

Tutorials on HTML5, Javascript, WinRT and .NET

Entity Framework, the Context, ComboBoxes and Include Foreign Key columns.

by Ioannis Panagopoulos

How do they all connect? Well it boils down to the following questions:


  • If I am using Entity Framework in a Desktop application when should I open the Context and for how long should I keep it open?
  • I have ComboBoxes that will be used to select values for the Reference Properties of my Entity Framework objects. How am I going to achieve this?
  • Should I bind to Reference Properties or should I include the Foreign Keys in my model (option named “Include Foreign Key columns in the model”) and bind to it from my ComboBoxes?


Let’s take for example to following DB schema and its Entity Framework model.

image image 

That is, Products are sold to Users in specific dates and those sales are stored in the Sales table. Note that in this model I have chosen to “Include Foreign Key columns in the model” and I also have the Navigation Properties (Reference Properties) available. The application presents us with a list of the Sales made by date and allows us to Edit/Add/Delete a Sale. When we click the Edit or Add button, a new window opens with the selected Sale’s details.

 “When and for how long should I leave the context open in this scenario?”

The Object Context in Entity Framework follows the “Unit of Work” pattern. The “Unit of Work” pattern states that  one should perform all changes to his business objects and then commit all those changes with one round-trip to the database. Those changes are related to each other by a common “business task” that needs to be done. In EF’s case, this means that all changes made to the objects belonging to a Context are going to be submitted to the database altogether when we call SaveChanges(), as a single database transaction (that is if one command fails all changes will be rolled over). Therefore a decision needs to be made about the time we open and close the Context. We distinguish 3 options:


  • “One Context Per Application” means that we open the Context when the application starts and whenever we need to persist our changes to the database we call the SaveChanges() method. The Context will be disposed when the application ends. This in most cases is not a good option. For example, in an application where you can have multiple windows open you can be editing let’s say two Sales at the same time. If these two Edit Windows share the same Context the Sale objects that are data bound to them will also belong to the same Context. This means that if you click Save in one of the two calling SaveChanges()  you will in fact be saving all the changes for the other Edit Window too. This can be tested in the EFCBTests1 project. Opening two “Edit Sale” windows changing some of their values and clicking “Save” in one of the two, will also persist the changes made to the other. Moreover, following this approach makes it really difficult transforming your application to “Service Oriented” since there is no way of keeping the context open throughout different service calls. If your application is really small and mainly opens modal windows or consists only of reads and not writes to the database it is the only case where this approach may suit you. In all other cases you must avoid it.


  • “One Context Per Method” means that in every method you open and close the context with a using statement. This is the extreme of the previous case and although valid does not allow you to take advantage of any of the goodies that come out of the box with you entities. For example your data bound to the GUI objects are not in the context and therefore you always need to attach them before persisting them. The situation gets even worse when you also want to control a list of objects in the GUI allowing the user to add/remove items. If the list is not in the context you have extra work to do later when you want to persist the changes. This approach also in long business transactional operations will tend to create huge methods for you since you will find yourself implementing a lot of login within that using statement. Therefore this approach is useful in Web applications although in that case the “One context per Http Request” is a better approach. Otherwise, it is not recommended.


  • “One Context Per Window” means that we open a new Context whenever a new Window is displayed to the user. The Context gets disposed when the Window closes. This is more close to the “Unit of Work” pattern, since we consider a Window as a single, simple task that the user needs to accomplish. This is the most recommended approach in Desktop applications. Of course sometimes you can break this rule by allowing the Context to be shared among windows (especially when the one is opened by the other and is modal).  If for example in the list of Sales you allow only one edit window to be open you could pass the context from the “List window” to the “Edit window” since you could consider the two windows as a single “Task” for your application. In fact the advantages of sharing a Context between windows is that whatever you do with your Context objects in one of your windows gets immediately reflected to the other. This is a neat feature (provided to you under the hoods by the INotifyPropertyChanged interface that the default Entity Framework objects implement – not the POCO ones) since it allows you to have fresh data in all of your open windows. This could be a good choice in EFCBTests1 project if the Edit window was opened as modal and for only one sale. Generally, this is considered the best approach for Desktop applications.


Therefore to summarize for Desktop applications:


  • Do not use “One Context Per Application” except for the cases where your application is really small, opens only modal windows (one window at a time) and it is not going to become large in the future.
  • Follow the “One Context Per Window” rule since in most of the cases the Window defines your “Unit of Work”, open the Context when opening the window and it will get disposed by itself when you close it. This also allows you to just close the window when the user hits “Cancel” having an “Undo” feature out of the box since all the changes will be lost with the Context.
  • Break the “One Context Per Window” rule in situations where you will open another window which you can consider as “part” of the task that needs to be done in the parent window. In this case you get the out of the box features of sharing the same objects between windows but always keep in mind that changes will be persisted throughout all open windows.


Having stated that the “One Context per Window” approach seems to be the best one for Desktop applications we face two challenges, the one of binding Navigation Properties to ComboBoxes or to other selection controls that have lists of selection objects and the other of keeping some global objects that we need to use as defaults for the Navigation Properties of our Entities.


“How do I bind my ComboBoxes, ListBoxes etc to Navigation Properties and how do I use default global objects as values for my Navigation Properties?”

In the EFCBTests2 project we open a new Context in the Edit Sales window and load the Products and Users entities that will be ItemsSources for our Combos from within that context. Data Binding and SaveChanges() work like a charm since the objects that are referenced by the Navigation Properties of the Sales object and are data bound to the Combos are in the same Context. But beware. Whenever the window opens a new round-trip occurs to the database to fetch all the Products and Users. This imposes an overhead and in cases where your data does not expect to change that often (having a selection list of Countries or Currencies for example) it is bad design and scales poorly. You need to be able to load in your application the Users and the Products only once (eg. at the beginning)  and use the in-memory lists whenever the Edit Sales window opens without visiting your database every time.

But if you load the lists at the beginning, they will belong to a different context and since the context will close they will become Detached Entities. If you now set them as the ItemsSources of your Combos and bind the Combos  to Navigation Properties, when you call SaveChanges you end up creating new objects since the ones that are listed by the Combos are considered new to the current context.

The same happens when you want to have a global object that you need to use as the default one for a Navigation Property throughout the application (an example will follow later in this post)

To tackle all this you have two choices:


1) “Include Foreign Key columns in the model” approach

Use the option “Include Foreign Key columns in the model”, load the source lists of the Combos in a static class and in the Edit Sales window make databinding work with the FK ids of the Sales object and not with the Navigation Properties. This is the approach followed in EFCBTests3 project. We use the Inititalizations static class to load the lists once:

public static class Initializations {
    public static List<Product> Products { get; set; }
    public static List<User> Users { get; set; }
    static Initializations(){
        using (Entities Ctx = new Entities()){       {
            Ctx.Users.MergeOption = MergeOption.NoTracking;
            Ctx.Products.MergeOption = MergeOption.NoTracking;
            Products = Ctx.Products.OrderBy(x => x.Title).ToList();
            Users = Ctx.Users.OrderBy(x => x.Username).ToList();

Note that the MergeOption directive allows as to create Detached entities from the beginning. Bind your combos in XAML in the Edit Sales window as follows:

<ComboBox ItemsSource="{Binding Products}" 
          SelectedValue="{Binding TheSale.ProductSold_Id,Mode=TwoWay}" 

Note that the ItemsSource property is set to list that references the static list (this connection is made at the .xaml.cs file), the SelectedValuePath is set to the primary key Id and the SelectedValue is databound to the FK ProductSold_Id (the foreign key included in the Entity).

Using the foreign keys for databinding enables you also to create lists of objects to use as ItemsSources that are more lightweight than the full blown ones. For example in ECBTests4 project we create a ProductLite class as follows:

public class ProductLite {
    public int Id { get; set; }
    public string Description { get; set; }

And we create the Products static list using those objects in the static Initializer:

Products = (from p in Ctx.Products orderby p.Title ascending 
            select new ProductLite() {

As you can see with this approach we save a lot of memory and we still can bind to our Entity Framework objects. So why should we bother with another approach (explained later in this post) that does not use the foreign keys for binding? Well, a lot of people would argue that the whole foreign key thing is a database artifact and should appear as little as possible in the Business Logic or the UI. If the Business Layer and the GUI are unaware of these FK artifacts it is a lot easier to migrate to other kinds of databases or change the Primary and Foreign key types.

“Is there another reason to use the foreign keys in my Entity objects?”

Another reason you may need the Foreign Key is when you intend to store an Entity object during the whole application lifecycle and use it when persisting other entities. A representative example is the case in the following db and the equivalent EF schema.

image image

When the application initializes we want to load a specific Currency. Then throughout the whole application whenever a new ProductsCur is created by the user, we need to set  its Currency to the global Currency object. Clearly the default Currency object is not part of the same Context of the one that will be created for the new product. Therefore we cannot use it as it is. This problem is solved with the foreign key inclusion since we can use that instead. In EFCBTests5 project we do exactly that. In the Initializations we have the DefaultCurrencyId which gets its value at the initiation of the application:

public static class Initializations {
    public static int DefaultCurrencyId;
    static Initializations()  {
        using (Entities Ctx = new Entities()) {
            DefaultCurrencyId = Ctx.Currencies.First().Id;

Now in the Edit Product window we bind the Currency Combo to the FK as in the previous case and whenever we initialize a new product we go as follows:

TheProduct = new ProductsCur() { ProductName="",Price=0,

So we have seen two very important uses of the Foreign key Id. How would we proceed if in both these cases we do not want to include the foreign keys in the model?

2) Not including any foreign keys approach

We have to tackle two cases. How to data bind to ComboBoxes and how to use global objects such as the Currency object. In the first we do not want to load the values of the Combo every time the window opens but we want to have the list of objects available from initialization. To tackle this we need to implement a new ComboBox, specific for the currencies. The ComboBox will display the static list of the currencies but whenever the selected value is changed it will use the new Context to fetch the object from the database. The static list is initializes as follows:

public static class Initializations {
    public static Currency DefaultCurrency;
    public static List<Currency> Currencies;
    static Initializations()  {
        using (Entities Ctx = new Entities()) {
            DefaultCurrency = Ctx.Currencies.First();
            Currencies = Ctx.Currencies.OrderBy(x => x.Code).ToList();

Now we create the CurrenciesComboBox as follows:

public class CurrenciesComboBox:ComboBox
    public Entities Context
            get { return (Entities)GetValue(ContextProperty); }
            set { SetValue(ContextProperty, value); }
    public static readonly DependencyProperty ContextProperty =
        DependencyProperty.Register("Context", typeof(Entities), typeof(CurrenciesComboBox), 
        new UIPropertyMetadata(null));
    public Currency SelectedCurrency
            get { return (Currency)GetValue(SelectedCurrencyProperty); }
            set { SetValue(SelectedCurrencyProperty, value); }
    public static readonly DependencyProperty SelectedCurrencyProperty =
        DependencyProperty.Register("SelectedCurrency", typeof(Currency), typeof(CurrenciesComboBox), 
        new FrameworkPropertyMetadata(_onSelectedCurrencyChanged));
    private static void _onSelectedCurrencyChanged(DependencyObject d, DependencyPropertyChangedEventArgs e){
        CurrenciesComboBox Sender = d as CurrenciesComboBox;
        Currency NewCurrecny=e.NewValue as Currency;
        Sender.SelectedItem = Initializations.Currencies.FirstOrDefault(x => x.Id == NewCurrecny.Id);
    public CurrenciesComboBox(){
        if (!System.ComponentModel.DesignerProperties.GetIsInDesignMode(this))
            ItemsSource = Initializations.Currencies;
            DisplayMemberPath = "Code";
            SelectedValuePath = "Id";
    protected override void OnSelectionChanged(SelectionChangedEventArgs e){
        if (SelectedItem == null)
            SelectedCurrency = null;
            Currency Temp = (SelectedItem as Currency);
            SelectedCurrency = Context.Currencies.FirstOrDefault(x => x.Id == Temp.Id);

We declare two dependency properties, one for the Context Binding (Context) and one for the Navigation Property (SelectedCurrency) binding. It is very important to first declare the Context since WPF assigns the bindings from top to bottom. If you do it the other way round you will get a null reference exception.

The main idea is that when SeletedCurrency changes we select from the Currencies static list the appropriate item and set it as the SelectedItem. When the SelectedItem changes we search in the context and find the appropriate item and set it as the SelectedCurrency. In the constructor we do the connection with the static list and the GetInDesignMode method allows our control to appear without problems in the VS Designer. This approach also allows us to work with Lite objects as in the previous scenario with small modification on what we select on each case.

After we create it, we use the CurrenciesComboBox as follows:

<Window x:Class="EFCBTests.WindowEditAddProduct"
<app:CurrenciesComboBox Context="{Binding Ctx}" 
                SelectedCurrency="{Binding TheProduct.Currency,Mode=TwoWay}"/>

Finally if we want to use a global default currency object, whenever we need to create a new Entitiy in a context the Currency Navigation Property should be defined as follows (previously where we display the Initializations class note that the DefaultCurrency object is created with the MergeOption set to NoTracking which means that it is detached from the beginning):

TheProduct = new ProductsCur() 

This approach is used in EFCBTests6 project.

This concludes a pretty large article on the Entity Framework and Context and some tricks in binding Navigation Properties to controls of the GUI.

Projects to download:

To run the project you will need also the following backup of the SampleApp database created in MSSQL 2008 (Database Backup File)

Shout it

kick it on

blog comments powered by Disqus
hire me