Adding an object with a foreign key in MVC

posted March 08th 2009

So you’ve worked out how to use MVC and the Entity Framework. You can add objects to the database, and you can get them out again, update them and save them back. Awesome! But have you found it cumbersome to create instances of objects just so that you can maintain referential integrity with your foreign keys?

I’ve been reading about on the web about inserting entities to the database, and including references to primary keys in another table. I’m actually surprised the number of ways I’ve seen this done. This is the most common way I’ve seen people do it:

Model.Order order = Model.Order.CreateOrder();
order.Customer = (from c in Customer where c.Id == custId select c).First();
db.SaveChanges();

When I first saw that, I thought… yeah ok. That could work. I’ve seen plenty of similar methods as well, revolving around selecting a customer, user, book or whatever by id, return the customer, user or book and then adding it to the object we want to save… and then saving it.

The problem here, as far as I can tell is that you end up making an unneccesary select from the database. If we already now the key for the Customer, then we SURELY can just insert that into the table along with the rest of the data?!? Am I right? Yes. I am.

Next time you go to create an Order, have a look just under Customer in the intellisense drop down…. you’ll see CustomerReference. If your data model is right, this should be an Entity Reference to your customer. This is the key to inserting your Customer ID (pun intended).

Model.Order order = Model.Order.CreateOrder();
Model.CustomerReference.EntityKey = new EntityKey("Model.Customer", "Id", custId);
db.SaveChanges();

That’s it. That’s all you need to do! The only trick to this is sometimes working out what the hell “Model.Customer” should be. The easiest way to find that is to type:

db.

…and let Intellisense come up and remind you what your entity sets are called. By default it can create Customer as CustomerSet or something equally strange (Of course, “db” is whatever your ObjectContext is called). Also make sure you get your casing correct, as customer != Customer

Alan

Comments(2)

Comments

Alex S-D

May 7th, 2009 | 3:20 am

how does this work for an update?

My code fails with error:
“The object could not be added or attached because its EntityReference has an EntityKey property value that does not match the EntityKey for this object.”

public void Update(tblCountry dsCountry)
{
context.Attach(dsCountry);
dsCountry.SetAllModified(context); // custom extension method

try
{
dsCountry.tblTimeZonesReference.EntityKey = new EntityKey(“tblCountry”, “TimeZoneID”, dsCountry.tblTimeZones.TimeZoneID);

context.SaveChanges(true);
}
catch (OptimisticConcurrencyException e)
{
// someone else changed the data, throw
throw (e);
}
}

This is taking a lot of my time up, any help would be great
Alan

May 7th, 2009 | 4:41 am

Attach uses the object’s EntityKey to create the relationship. As you’re working with a detached object this entitykey needs to match the persisted object in the database. By changing the entitykey of the object, it no longer matches the persisted entity reference which may be causing the attach to fail.

I think

My understanding is that when using Attach the object is attached in an unchanged state. If you want to make changes, you need to use ApplyPropertyChanges to update the existing object.

You may want to try something like this, http://msdn.microsoft.com/en-us/library/bb896248.aspx

Topics

.NET MVC 5st App Design ASP.NET C# ClickTest css Database Development Entity Framework ExtJS fivesecondtest Game Design Interfaces iPhone JQuery MigratorDotNet Navflow plupload Ramble Ruby SQL typography update usability UsabilityHub

Angry Monkeys