Foreign Key Relationships in the Entity Framework
The information in this post is out of date.
Visit msdn.com/data/ef for the latest information on current and past releases of EF.
Last March Alex James posted to our design blog about our plans for adding foreign keys to the entity framework. Since then we’ve pushed forward with implementing FK associations and properties and have included them in Visual Studio 2010 Beta 2. This article offers a walkthrough of foreign key relationships in the EF and how they’re useful in VS2010 Beta2. For more information, the documentation and a number of blogs offer useful background about the logic behind adding EF foreign key relationships; here are a few links:
https://blogs.msdn.com/efdesign/archive/2009/03/16/foreign-keys-in-the-entity-framework.aspx
Defining and Managing Relationship MSDN documentation
Walkthrough:
We’re going to look at the designer experience, the EDMX model differences and a few coding examples. We’ll use this extremely simple database model:
Note the fact that the Posts table has a foreign key to the Blogs table.
Designer Experience
Start by creating a new WPF application then add a new ADO.NET Entity Data Model item to it. Choose to generate the model from the database. Open the Tables portion of the treeview and check the Blogs and Posts tables. Note when choosing the tables with which to generate Entities “Include Foreign Key Columns in the Model” is checked by default. This means new models by default will use the new FK associations rather than Independent Associations. Independent Associations were previously the only type of association offered in the Entity Framework and still can be used. If you want just Independent Associations in your model, you’ll need to uncheck the Include FK Columns in the Model checkbox.
After clicking Finish your model should look as follows:
Double click the association and you’ll see the foreign key properties for the relationship:
Let’s leave this as is for now but note it’s possible to switch an existing Association between an FK or Independent Association type. In this case if you wanted to switch an Association to be Independent, you’d press the Delete button in the Referential Constraint dialog. After doing so you’d need to map the Independent Association appropriately and remove the BlogID property from the Post Entity. For now let’s move on without changing the association type.
For comparison sake, add a new entity to the design surface from the toolbox. Once done, right click on the entity and choose Add->Association:
Note that your association doesn’t have to be an FK association (association types can be mixed in the same model). Uncheck the ‘Add foreign key properties to the ‘Blog’ Entity’ check box then Click OK in the dialog. This will let us later look at the model differences between Independent and Foreign Key associations.
So far we’ve: created a WPF project, added an EDM Data Model to it, reverse engineered our Blogs and Posts tables to the model, then added a third entity and created an Independent Association with it.
XML model differences:
We’re going to briefly diverge and look at some of the differences in the XML generated for the different types of associations in our simple model. If you don’t want to do this, feel free to skip to the next section.
Right click on the EDMX file in your project and choose Open With->XML Editor. This will close the design surface and open the contents of the EDMX file in the XML Editor.
Scrolling down, the CSDL XML fragment for our Independent Association looks like this:
<Association Name="Entity1Blog">
<End Type="BlogsModel.Entity1" Role="Entity1" Multiplicity="1" />
<End Type="BlogsModel.Blog" Role="Blog" Multiplicity="*" />
</Association>
It would also have an MSL XML fragment that tells EF how to traverse the relationship if we’d mapped the new entity & association to storage objects. Since we didn’t complete the mappings the fragment is missing.
The CSDL XML fragment for our FK Associations looks like this:
<Association Name="FK_BlogPost">
<End Role="Blogs" Type="BlogsModel.Blog" Multiplicity="1" />
<End Role="Posts" Type="BlogsModel.Post" Multiplicity="*" />
<ReferentialConstraint>
<Principal Role="Blogs">
<PropertyRef Name="BlogID" />
</Principal>
<Dependent Role="Posts">
<PropertyRef Name="BlogID" />
</Dependent>
</ReferentialConstraint>
</Association>
Notice the ReferentialConstraint element and its contents. There is also no MSL XML fragment for the relationship.
One other thing of interest to note is the Post EntityType has this fragment in the CSDL:
<Property Name="BlogID" Type="Int32" Nullable="false" />
Having the BlogID property on the Post Entity will allow us to directly read & manipulate its value plus use it for data binding (no more need for partial classes with this exposed as a property).
This divergence this tells us three things:
- EF is handling the two different types of Associations completely differently.
- If you want to dig into an EDMX and tell what types of Associations you have you can (but looking at the XML can hurt your eyes J).
- Foreign Key Associations add the parent ID key properties as properties on the child object. This is useful in a number of ways.
Coding experience
Double click on the EDMX file in your project. After clicking Yes, this should reopen the EF Designer for your model. Delete Entity1 from the design surface. This should give you valid model with everything mapped (exactly what you had after running the Add->New Item wizard).
We’re going to run through a few scenarios of how to code against our model. To facilitate, add a button to your WPF form and double click it.
Scenario 1: Adding new Blog and a new Post together by setting Navigation properties.
Note this is what you used to do without FK Associations and is still the recommended way of adding two new dependent objects together. We’ll talk about why in a few minutes. Inside the button event handler add the following code:
using (BlogsEntities ctx = new BlogsEntities())
{
Blog myBlog = new Blog { BlogID = 9, Name = "Tim's blog", Owner = "Tim" };
Post myPost = new Post { PostID = 102, Title = "Post Title", PostContent = "TestContent",
= DateTime.Now, ModifiedDate = DateTime.Now };
//Nav properties will work immediately.
myBlog.Posts.Add(myPost);
ctx.Blogs.AddObject(myBlog);
ctx.SaveChanges();
}
This code opens a connection to the DB, creates a new blog object, creates a new post object, adds the post object to the blog object’s Posts collection, adds the new blog objects to the context, then saves the changes to the database. Note that as soon as we added the myPost object to the Posts collection of myBlog we could use the navigation properties to traverse between the objects.
Scenario 2: Adding new Blog and a new Post together but set the Post BlogID FK property instead of adding to the myBlog Posts collection.
Delete the code you just placed in the event handler and replace it with the following:
using (BlogsEntities ctx = new BlogsEntities())
{
Blog myBlog = new Blog{BlogID = 11, Name = "Tim's blog", Owner = "Tim"};
Post myPost = new Post{PostID = 101,Title = "Post Title", CreatedDate = DateTime.Now,
ModifiedDate = DateTime.Now, PostContent="Post Content", BlogID = 11};
ctx.Posts.AddObject(myPost);
ctx.Blogs.AddObject (myBlog);
ctx.SaveChanges();
}
Note that the navigation properties on the two new objects won’t map to each other until after SaveChanges is called. This is because the context doesn’t know about the parent object yet.
Scenario 3: Adding a new post to an already existing blog.
Replace the code in the event handler with the following:
using (BlogsEntities ctx = new BlogsEntities())
{
Post myPost = new Post {PostID = 102, Title = "Post Title", CreatedDate =
DateTime.Now, ModifiedDate =DateTime.Now, PostContent = "Post Content", BlogID = 11};
//Nav properties will work immediately b/c the Blog object already exists
ctx.Posts.AddObject(myPost);
ctx.SaveChanges();
}
Note we never loaded the Blog object into memory. We knew BlogID 11 was a valid ID and set the post.BlogID property directly. Note also, the navigation property for myPost.Blog will work immediately after setting the FK property because the Blog object already exists in the context. Being able to set the property directly enables some previously difficult data binding scenarios as well as can make some coding experiences easier.
These three coding examples show different ways of creating new objects and setting up their relationships using navigation properties and FK associations.
Summary
In .NET 4.0 we’ve added support for FK Properties and FK Associations to the Entity Framework. It’s still possible to use Independent Associations and the two can be mixed in models. We’re excited to offer FK Associations because they simplify many common Entity Framework coding tasks. Hopefully this walkthrough has given you a feel for how you can use FK Associations and Properties.
As always we'd love to hear your thoughts.
Thanks,
Tim Laverty, Program Manager
Comments
Anonymous
November 06, 2009
This should make databinding much easier like by offering a dropdown combobox for productid in the order details and the user can chagne it in new mode or edit mode of the order without problems there is something i don't understnad , in the 3rd example you saying "Note we never loaded the Blog object into memory" , but later said "Blog object already exists in the context" ! how come? do you mean we could set the BlogID to 11 because the PK is already exist in the "Database" ?Anonymous
November 08, 2009
This was what i was looking forAnonymous
November 08, 2009
Won't it increase the size of response from database that everyone intends to keep as small as possible ?Anonymous
November 08, 2009
@Bassam- Yes, nav relationships immediately function for the child post entity b/c the blog entity w/ id 11 is in the DB (and loaded in the context). @ Jacob- There's an incremental cost to bringing back the FK values which has to be weighed against the easier coding patterns & data binding capabilities.Anonymous
November 08, 2009
Hello I'm new to your blog and ADO.NET technologies in general. I'm facing a design situation which might have been encountered by many. First off, I'm completely off topic and I sorry but a search on your blog on "data cache" didn't help much (as client data caching my problem). I'm dealing with very frequent inserts I must generate in the client and want to do that within some sort of cache first (to avoid calling the database server all the time) but in the same time I need some sort of transaction behavior, ie make sure that data gets inserted in the end no matter what (a sort of in-memory DataSet with a commit on it after each insert which doesn't reach the database until a different call, say "CommitToDatabase"). I'm not sure if that is achievable using ADO.NET. Any help is appreciated, thanks.Anonymous
November 08, 2009
Hello, must a foreign key in the conceptual model be backed by a foreign key in the database? Having the foreign key in the database let to sql exceptions when deleting an order and it's orderlines. It seems that the delete order was executed before the delete orderlines. The foreign key constraint prohibited the delete order because of the still existing orderlines. Is there another way to deal with this problem?Anonymous
November 09, 2009
@dacian- The EF in effect is a client cache, meaning none of your changes go to the DB until you call context.SaveChanges. EF also plays nicely with the TransactionScope object in System.Transactions should you need control across contexts.Anonymous
November 09, 2009
@Markus- EF will order the parent & child deletes when you call SaveChanges. This is even the case if the parent is deleted previous to the children as in the example below. Can you email me what's happening? timlav@microsoft.com int iblogid = 7; var postlist = (from qryposts in ctx.Posts where qryposts.BlogID.Equals(iblogid) select qryposts).ToList(); var myblog = (from myblogs in ctx.Blogs where myblogs.BlogID.Equals(iblogid) select myblogs).First(); ctx.DeleteObject(myblog); Array.ForEach(postlist.ToArray(), c => ctx.DeleteObject(c)); ctx.SaveChanges();Anonymous
November 09, 2009
The comment has been removedAnonymous
November 09, 2009
I would like to build the following composite: List<Blog> allBlogs=new List<Blogs> where Blog have a ListPosts> var blogs=(from b in ctx.Blogs.Include("Posts") select new Blog { BlogId=b.Id, Name=b.Name, Posts=(from p in b.Posts where b.Id==p.BlogId select new Post { PostId=p.PostId, PostContent=p.PostContent, Title=p.Title }).ToList() } ).ToList And it fails. When we add the second .ToList()Anonymous
December 06, 2009
Where are the database files for this walkthrough? (Choose to generate the model from the database)Anonymous
June 02, 2010
Wow..... Great..... Finally i got this code. Thanks for sharing helpful information here. Here i found a really great information for me.Anonymous
June 09, 2010
I have a similar situation, but I am working with views, not tables, so I don't have foreign keys inferred from the database structure. My situation would be if 'Owner' under the 'Blog' object was instead an 'OwnerID' to a new object called 'Owner', which contained the id, and an OwnerName field, for arguments sake. I want to add an association from 'Owner' to 'Blog', but any time I try to do the association mapping, I have to map the primary/entity key of 'Blog', 'BlogID', which has nothing to do with the association I am trying to add. I might just be missing something simple, or possibly this is something the Entity Framework just doesn't handle. Any help or advice would be greatly appreciated.Anonymous
August 28, 2011
is there a way to prevent to load all cascading object with foreign keys similar to FetchLazy in JPA?Anonymous
February 14, 2012
I find it extraordinarily rare that you would assign IDs in code. I suspect this is a lack of knowledge on foreign key entities within the framework.Anonymous
June 03, 2014
how to order the above fields based on Blog Name in entity data model Im having 2 tables , im struggle to order data based on foreign key tableAnonymous
June 05, 2014
@Anandhi - I'm not exactly sure what you are asking. If you want to order data when you are querying, then you can use the LINQ orderby operator: var query = from p in db.Posts orderby p.Title select p; If you want to order by data from a related table, you can just 'dot thru' the navigation property to specify the property to order by: var query = from p in db.Posts orderby p.Blog.Name select p;