Foreign Keys in the Entity Framework

  • Article

Background

A number of months ago we asked whether Foreign Keys (FKs) in Conceptual and Object models were important.

The feedback we got indicated that there are a real mix of opinions on the topic. Some people are all for them while others think that FKs pollute the conceptual model.

The fact that our customers were so divided meant we thought it was important to provide options here. Those who want FKs in their Entities should be able to do so, so they can gain all the benefits that having FKs in your Entities undoubtedly provide. On the other hand customers who are concerned that have FKs in their Entities in someway pollutes their model can continue to use the type of associations we had in .NET 3.5 SP1.

In the past we've called the .NET 3.5 SP1 style Associations "first class" associations. Which while accurate somehow implies that associations based on FKs in Entities are "second class". In fact when we talked about this new work recently with our MVPs one of the first questions was "are these second class?", which they most definitely are not.

The fact is these new associations are different but first class nevertheless.

So what's been added?

In .NET 4.0 we will add support for a new type of association called "FK Associations".

The way this works is we will allow you to include your FK columns in your entities as "FK properties", and once you have FK Properties in your entity you can create an "FK Association" that is dependent upon those properties.

Okay so we will have "FK Associations", what are we going to call the older style associations? Obviously saying something like ".NET 3.5 SP1 style Associations" every time isn't ideal. So we are going to call them "Independent Associations".

The term "Independent Association" resonates for us because they are independently mapped, whereas FK Associations need no mapping, simply mapping the Entity(Set) is sufficient.

How do I use an "FK Association"?

The real reason so many customers and partners are asking for "FK Associations" is that they significantly simplify some key coding patterns. So much so that we are convinced that for most people "FK Associations" will be an automatic choice going forward.

Some of the things that are hard with "Independent Associations" are trivially easy using FK Associations. Scenarios as diverse as DataBinding, Dynamic Data, Concurrency Control,  ASP.NET MVC Binders, N-Tier etc are all positively impacted.

Let's have a look at some code snippets that will work with "FK Associations":

1) Create a new Product and FK Association to an existing Category by setting the FK Property directly:

using (var context = new Context())
{
    //Create a product and a relationship to a known category by ID
    Product p = new Product
    {
        ID = 1,
        Name = "Bovril",
        CategoryID = 13
    };
    //Add the product (and create the relationship by FK value)
    context.Products.AddObject(p);
    context.SaveChanges();
}

This sort of approach works both for insert and update, and is particular useful for things like databinding, where you often have the new Value of the FK in a grid or something but you don't have the corresponding object, and you don't want to wear the cost of a query to pull back the principal object (i.e. the Category).

2) Create a new Product and a new FK Association to an existing Category by setting the reference instead:

public void Create_new_Product_in_existing_Category_by_reference()
{
    using (var context = new Context())
    {
        //Create a new product and relate to an existing category
        Product p = new Product
        {
            ID = 1,
            Name = "Bovril",
            Category = context.Categories
.Single(c => c.Name == "Food")
        };
        // Note: no need to add the product, because relating  
        // to an existing category does that automatically.
        // Also notice the use of the Single() query operator
        // this is new to EF in .NET 4.0 too.
        context.SaveChanges();
    }
}

This programming pattern is not new to the Entity Framework, you could do this in .NET 3.5 SP1. I called it out because it shows you can still write the sort of code you write with Independent Associations even when using FK Associations.

3) Update an existing Product without informing the Entity Framework about the original value of the CategoryID (not supported with Independent Associations):

public void Edit(Product editedProduct)
{
    using (var context = new Context())
    {
        // Create a stand-in for the original entity
        // by just using the ID. Of the editedProduct
        // Note: you don't have to provide an existing Category or CategoryID
        context.Products.Attach(
new Product { ID = editedProduct.ID });

        // Now update with new values including CategoryID
        context.Products.ApplyCurrentValues(editedProduct);
        context.SaveChanges();
    }
}

In this example "editedProduct" is a product which has been edited somewhere, this is exactly the sort of the code you might write in the Edit method of a ASP.NET MVC controller, and is a great improvement over the code you have to write using Independent Associations.

There is much more you can do with FK Associations, but these 3 samples give you a flavor of the sort of coding patterns FK Associations allow.

Keeping FKs and References in Sync

One thing that hasn't been mentioned so far is that the Entity Framework tries to keep related References and FKs in sync as much as possible.

When this synchronization occurs depends upon when the Entity Framework is notified of changes, which depends upon the type of Entity Classes involved: be they POCO with Proxies, POCO without Proxies, IPOCO or EntityObjects etc.

Another post will cover this in more detail.

How do I create an "FK Association"?

There are a number of ways.

One mainline scenario is when using the tools to infer a model from a database. We have added an option to choose whether "FK Associations" or "Independent Associations" are generated by default. The same is true if you use EdmGen.exe (i.e. our command line tool).

The second mainline scenario for creating FK Associations is creating a model from scratch, aka Model First which is new to .NET 4.0.

Here are the steps involved:

  1. Create two Entities (say Product and Category) that look something like this:

    ProductCategoryStep1

  2. Add a property that will be the FK Property: i.e. CategoryID

    ProductCategoryStep2

  3. Create an association between the two EntityTypes with the correct end multiplicities and NavigationProperty names:

    ProductCategoryStep3

  4. Double Click the line between the two Entities, that represents the Association, and add a Referential Integrity Constraint:

    ProductCategoryStep4
    This is the step that tells the Entity Framework that the CategoryID is an "FK Property" and that the "CategoryProduct" association is an "FK Association".

  5. You end up with something that looks like this:

    ProductCategoryStep5 

And you are done you've set up an FK Association. Notice that this association doesn't need to be mapped you simply need to make sure you map all the properties of both Entities.

Very easy.

Summary

In .NET 4.0 we will add support for FK Properties and FK Associations to the Entity Framework. The existing style Independent Associations will still be possible, but we expect that FK Associations will become most peoples automatic choice because they simplify so many common Entity Framework coding tasks.

As always we'd love to hear your thoughts on this work.

Alex James,
Program Manager, Entity Framework Team, Microsoft.

This post is part of the transparent design exercise in the Entity Framework Team. To understand how it works and how your feedback will be used please look at this post .

Comments

  • Anonymous
    March 16, 2009
    PingBack from http://www.clickandsolve.com/?p=23814

  • Anonymous
    March 16, 2009
    Thank you for submitting this cool story - Trackback from DotNetShoutout

  • Anonymous
    March 16, 2009
    Code snippet #3, with the editedProduct doesn't seem to make much sense to me. Where does newProduct come from? Shouldn't you just attach the editedProduct directly (which will have the updated Category/ID in it)?

  • Anonymous
    March 16, 2009
    Michael, there was a typo... it was suppose to be editedProduct... Let me know if it still doesn't make sense Cheers Alex

  • Anonymous
    March 16, 2009
    I'm with Michael here - why not context.Products.Attach(editedProduct); ?

  • Anonymous
    March 16, 2009
    Really good news ! I had to manage this on current EF release and it gave me a big headhache to keep FK and references sync. I still have some problems (setting FKs in constructor for example) but I'll now wait for next release instead of adding some code in my framework. Chris

  • Anonymous
    March 17, 2009
    Kristofer and Michael, The idea is to create a fake entity and attach that, to save the query to the database. And then to modify that with the values, including the FK value, in editedProduct with a call to ApplyCurrentValues(). At this point the EF knows the new state of the product and knows that it is modified. Does that make sense? Alex

  • Anonymous
    March 17, 2009
    But you can do that by attaching the existing editedProduct instance too. It is a basic n-tier scenario; get object, serialize/disconnect, do stuff, re-attach, update. Much cleaner if it can be done in one step IMO...

  • Anonymous
    March 18, 2009
    Alex, Good work! I'm really happy that the new version of the Entity Framework will a lot better and easy to use than the current version. Currently, I'm working with the version in .NET 3.5 SP1 it provides a really great level of abstraction, but sometimes it's difficult to write code mainly because of absense of foreign keys in the conceptual model.

  • Anonymous
    March 18, 2009
    Многие, кто сейчас работают с Entity Framework, жалуются на отсутствие внешних к

  • Anonymous
    March 20, 2009
    Great to hear this, it will make dealing with foreign keys much easier!

  • Anonymous
    March 23, 2009
    The comment has been removed

  • Anonymous
    March 23, 2009
    Jon, Generating a database from a model is a supported scenario in the next version of the Entity Framework (.NET 4.0) see the post on "Model First" on this blog. As for your other concerns and questions. we know we didn't get everything right in .NET 3.5, but we are listening to what our customers have been saying and are doing our best to rectify the situation for .NET 4.0, which will be a major improvement. Other than "model first" have you got any specific concerns with the first version of the Entity Framework? If so I'd love to hear from you. My email is alexj at microsoft dot com. Cheers Alex

  • Anonymous
    March 23, 2009
    I’d like to turn your attention to two blog posts on EF Design Blog about exciting new features planned

  • Anonymous
    March 24, 2009
    GREAT !!! i can't wait any longer for .NET 4.0 !!!

  • Anonymous
    March 24, 2009
    Background If you've been reading the EF Design Blog you will have seen that we recently announced something

  • Anonymous
    March 26, 2009
    Problem The most common way to delete an Entity in the Entity Framework is to pull the Entity you want

  • Anonymous
    March 30, 2009
    Cet article a pour objectif de décrire notre approche à propos de l'accès aux données. En préambule

  • Anonymous
    April 02, 2009
    The Entity Framework is pretty big, so when the Entity Framework team talks about things internally we

  • Anonymous
    April 16, 2009
    Background A while back I was writing a web app to try the Entity Framework and MVC together. I knew

  • Anonymous
    May 09, 2009
    The comment has been removed

  • Anonymous
    May 14, 2009
    @Baum, There are not plans for Microsoft to ship EF providers for databases outside of the SQL family--except for some work that the Host Integration Server team is doing around DB2.  For databases such as Oracle, we will depend on 3rd parties (or Oracle itself) to create those providers.

  • Danny

  • Anonymous
    June 14, 2009
    I'm using vs2010 beta 1. I created a .net 4.0 console project, added an empty ADO.NET Entity Model, then performed the steps detailed above for FK Associations for the Model First scenario. I found that I could not make the foreign key association in step 4, the CategoryID was not in the Dependant Property dropdown and typing it in didn't work either, is this a bug or did I miss something out?

  • Anonymous
    June 14, 2009
    @Alexander, Beta 1 doesn’t actually include support for Foreign Key Associations. You can expect the feature to be there in the next publically available release. Thanks, Diego

  • Anonymous
    June 18, 2009
    Thanks for the interesting information. Will it be possibly for the EF to generate the FK properties automatically? If you have a huge data model this will be a big hassle. Maybe, I'm a special case because we have our own data layer, but I'm fundamentally interested in having classes generated that can be mapped directly onto the underlying data columns. Currently, I've been trying to figure out how to have the current entity framework completely ignore foreign key associations. Anybody know if this is possible? Thanks, Paul

  • Anonymous
    June 19, 2009
    What are Stub Entities? A stub entity is a partially populated entity that stands in for the real thing.

  • Anonymous
    June 19, 2009
    What are Stub Entities? A stub entity is a partially populated entity that stands in for the real thing

  • Anonymous
    June 21, 2009
    The Entity Framework is pretty big, so when the Entity Framework team talks about things internally we

  • Anonymous
    June 21, 2009
    Problem The most common way to delete an Entity in the Entity Framework is to pull the Entity you want

  • Anonymous
    July 10, 2009
    I think a better way to go about this would be to be able to create entity proxies that only have the ID/primary key properties populated. Once you access another property, it would load the entity data from the database. This way you can assign relations without hitting the database or redundant properties.

  • Anonymous
    September 14, 2009
    I've tried to use the "FK Association" in the VS2010 Beta1 and DataServices CTP2 release - it doesn't  work as were outlined in this article. You can't create an FK association on the dependent object property if its not a part of the entity key!? Why this was changed and how can I work with the "FK association" now?

  • Anonymous
    September 14, 2009
    #Fedir, This wasn't "changed".  This feature just hasn't been released yet.  FK Associations will be in beta 2 of VS2010, but it hadn't yet made it into the product as of beta 1.  Sorry, but you have a little while to wait yet.

  • Danny

  • Anonymous
    October 11, 2009
    If I've not missed something, so please note that IObjectSet in Beta1 contains small amount of possible methods from ObjectSet, so the scenario #3 could not be covered unit tests (by mocking ObjectSet) at all because of using ApplyCurrentValues method. Is it possible to enlarge IObjectSet interface with that methods or introduce new interface for that. Thanks.

  • Anonymous
    October 12, 2009
    I hope this will also improve the performance of the Data Services. Actually the Data Services doesn't include any information about related object / foreign keys, to have them you have to $expand the relation, what sometimes generate only a bigger response and it is useless. For example I want to load a product, on the client I have already loaded all the available product categories, so I really don't need to $expand the product category relation to have it but if I don't do it I don't have it. Now if my product have 6 different relations to some mapping tables (which I have already loaded on the client) and I show 20 products per page, the Data Services includes to each request 20x6=120 completely useless informations, which make only the response bigger and worse the performance of the application. Instead of the foreign key could have also been used a "ghost" entity, which contains only the primary key. A "ghost" entity can interpreted as "this entity exists but it has not yet materialized". So loading entities from the DB without including referenced entities will generated "ghost" entities for all the relations which are not null. So for example to change the category of a product could be done by just assigning a new "ghost" entity to the product: //Create a "ghost" entity with a static method product.Category = Category.Ghost(3); //Create a "ghost" entity with a regular constructor product.Category = new Category(true){Id=3}; Moreover it would be nice if "ghost" entity could also been materialized, this means load the entity from the DB: //Loading a product without including the relations Product product = context.Product.Where(p => p.Id==3).First(); //Materializing the category relation = loading it product.Category.Materialize();

  • Anonymous
    October 28, 2009
    The comment has been removed

  • Anonymous
    May 01, 2010
    I'm gettin an error trying to create an "FK Association": Error 11009: Property 'categID' is not mapped. Can you help me?

  • Anonymous
    October 01, 2010
    Starting out with EF 4.0 I had presumed associations were simply these "FK Associations". I had no idea they were being implemented as independent tables. I was wondering where the FK key mappings were. Independent associations are only useful for Many-to-Many associations. Sorry, I am not impressed by the EF team.

  • Anonymous
    August 04, 2011
    Please - correct your oversight and add FK properties to EF 3.5.

  • Anonymous
    October 06, 2011
    I am using EF 3.5. I have problem in Updating FK. Whenever i try its show error "A referential integrity constraint violation occurred: A property that is a part of referential integrity constraint cannot be changed when the object has a non-temporary key."

  • Anonymous
    October 06, 2011
    I am using EF 3.5. I have problem in Updating FK. Whenever I try its show error "A referential integrity constraint violation occurred: A property that is a part of referential integrity constraint cannot be changed when the object has a non-temporary key."

  • Anonymous
    October 16, 2011
    The comment has been removed

  • Anonymous
    April 23, 2013
    Using EF4, new associations for new tables are often (but not always) not found when the model is updated from the database.  Thanks for giving me this workaround. Even so, it would be nice if the update from database did not have this problem.

  • Anonymous
    November 13, 2014
    I'm hoping that In Database First, the FK Association carry will over the FK name instead of auto-generating an association name. I think I noticed that it does that in Code-First, but not in Database-First.