Skip Navigation LinksHome > View Post

Working with Ids in the Entity Framework

I recently converted the ASP.NET application that powers theJoyOfCode.com to use the ADO.NET Entity Framework (EF). I've long been a fan of Object Relational Mapping and, whilst I'm not crazy about EF's lack of persistence ignorance, I am very impressed with EF and pleased with the decision to move.

Over the next few weeks I hope to write about some of the snags I encountered whilst working with EF and how I worked around them (or not).

Since I was working on an existing web application that had reasonably nice tiering and a clear, separate Data Access Layer that neatly encapsulated all the data access code, I wanted the move to EF to take place behind this encapsulation. This meant hthe client (the rest of theJoyOfCode.com application tier in this case) would be blissfully unaware of the changes. So, no changes to the signature of our DAL allowed. Finally, until the move to EF we made use of Stored Procedures and I wanted to get rid of them entirely, if possible. And it was. And I'm very happy with that.

Deleting by ID

Being a web application (and thus stateless) I pass a lot of Ids about, for example: DeletePost.aspx?id=4546483 (this isn't actually how posts get deleted but it serves as a suitable example). Naturally then, we have a corresponding method on our DAL that supports this:

BlogDal.DeletePost(int postId);

This used to be easy before, we just had a Stored Procedure that accepted the ID as a parameter and deleted the post. EF could have generated a method that called this Stored Procedure directly but, if you remember, we wanted to get rid of all those Stored Procedures.

The problem is, in EF, to delete an entity you have to have an instance of it in the first place. I could go and select it out of the database and then delete it:

var post = (from p in blogContext.Posts
            where p.PostId == postId
            select p).First();
            
blogContext.DeleteObject(post);
blogContext.SaveChanges();

But that would mean we fetch a fresh copy of the data all the way from the database just to delete it. Which is nuts.

So, I tried to create an 'empty' post with the appropriate Id and delete that:

Post post = new Post();
post.PostId = postId;

blogContext.AttachTo("Posts", post);
blogContext.DeleteObject(post);
blogContext.SaveChanges();

Now, in many cases this would work fine, but not here. The reason for is that the Post table has a foreign key to the User table. And, despite the fact that the post could be deleted and maintain referential integrity, I got an UpdateException: Entities in BlogEntities.Posts participate in 'FK_Posts_Categories' relationship. 0 related 'Categories' were found. 1 'Categories' was expected. Hmmph.

I wondered if I could fudge the relationship using an EntityKey and a nonsense user Id (0)?

Post post = new Post();
post.PostId = id;
post.UsersReference.EntityKey = new EntityKey("BlogEntities.Users", "UserId", 0);
blogContext.AttachTo("Posts", post);
blogContext.DeleteObject(post);
blogContext.SaveChanges();

Nope! This resulted in a ConcurrencyException: Store, Update, Insert or Delete statement affected an unexpected number of rows (0). A look at SQL Profiler showed this was because EF was adding the UserId to the where clause of the delete statement. Hmmmph. I was curious why this was so and asked a member of the EF team:

The reason for this is that relationships are first class parts of the model with their own concurrency tracking and everything. When you delete an entity, you must also delete those relationships in which the entity uniquely participates. Those relationships have the keys of their ends as concurrency values. So if you don’t have the key of the other end, then when the system tries to delete the relationship, the default key value doesn’t match the original key value and you get a concurrency exception.

Fair enough. I believe a popular approach to solving this in a web application is to store the object in ViewState at any point where it might be deleted and re-attach it during a PostBack. I didn't want to change the application and decided to go with selecting the data out of the database first. I reasoned that this was such an infrequently invoked operation (deleting posts) that the expense of a fetch was acceptable. YMMV.

Updating associations by ID

I encountered a similar problem (that I was able to solve) with Ids when trying to update an association, for example the User we discussed before. During an update to a post all the necessary fields are posted back to the page:

BlogDal.UpdatePost(int postId, string title, string body, int userId)

This is enough information to accurately recreate the post and attach him.

Post post = new Post();
post.PostId = id;
post.Body = body;
post.Title = title;
post.User = (from u in blogContext.Users where u.UserId == userId).First();
blogContext.AttachTo("Posts", post);
blogContext.SaveChanges();

But look, I had to do yet another selection from the database to grab the associated user! Fortunately the EntityKey trick we tried to use before can work here because we have the userId to hand:

Post post = new Post();
post.PostId = id;
post.Body = body;
post.Title = title;
post.UsersReference.EntityKey = new EntityKey("BlogEntities.Users", "UserId", 0);
blogContext.AttachTo("Posts", post);
blogContext.SaveChanges();

Booyaka.

 
Josh Post By Josh Twist
10:07 AM
09 Jan 2009

» Next Post: Things I love about Windows 7 (beta)
« Previous Post: WebTests expose silly security vulnerability

Comments are closed for this post.

Posted by Harley Green @ 09 Jan 2009 3:47 PM
JOsh - I agree it seems nuts to retrieve an object just to delete, but it seems that most projects I'm on end up having a bunch of validation around deleting objects, so I end up having to retrieve them anyway. At least it's consistent - the controllers don't need to know if they can delete by id or by object - it's all the same.

Posted by Colin @ 26 Jan 2009 7:53 AM
Same problem for us too...in the end I think I can live with the fetch for most situations of a delete and where I need more perf call a sproc.

Posted by Colin Whitlatch @ 30 Mar 2009 6:58 AM
Looks like you're missing the userid parameter from that second attach example:

post.UsersReference.EntityKey = new EntityKey("BlogEntities.Users", "UserId", 0);

Shouldn't the third parameter be the userid?

Posted by Adam @ 09 Apr 2010 1:57 AM
A better solution is to use eSQL with an ObjectQuery. Some people might not feel comfortable with two query languages, but until the framework can translate LINQ statements into DELETE and UPDATE statements you're a bit stuck.

Posted by chris @ 28 Apr 2011 4:05 PM
With something as trivial as a delete, why wouldn't you just bring the stored procedure into EF as a function and then just call: Context.DeletePosts(4546483);

BOOM!

Posted by Josh @ 28 Apr 2011 5:36 PM
Because I don't want to create crumby delete SPROCS for all my types?

Boom.

© 2005 - 2014 Josh Twist - All Rights Reserved.