Updates in Entity Framework
A primer on how to update objects, including adds and deletes, in the Entity Framework.
Having just done a column
on how LINQ + Entity Framework (EF) makes typical database activities in business applications, I realized that I'd concentrated entirely on retrieving data and ignored doing updates. There's a reason for that: LINQ doesn't do updates. Updates are the responsibility of EF.
EF converts tables into collections of objects, and some things are the same in every collection. With EF entities, as with any other object, to change a property you just set the property to the new value. This statement retrieves a Customer object and updates the ContactName property:
Dim en As New northwindEntities
Dim cust = (From c In en.Customers
Where c.CustomerID = "ALFKI"
cust.ContactName = "Peter Vogel"
EF will generate an SQL Update statement to transfer this property change to the appropriate column in the right row in the database. Since EF doesn't update every column in the table because one column is updated, the generated SQL statement will look something like this:
Set ContactName = 'Peter Vogel'
Where Id = 'ALFKI';
Deletes are only slightly more complicated. Collections will have a method for removing items from the collection; typically a method called something like Remove, RemoveAt, or Delete. Sometimes that method is on the collection itself, sometimes it's on the individual members of the collection. The parameter you pass to this method also varies: sometimes you can pass a key that identifies the object to be removed, sometimes you pass the position of the item to be removed, sometimes you have to pass the object to be removed.
EF puts its delete method—DeleteObject—on the collection manager (the ObjectContext object) and requires you to pass the object to be deleted. So, to delete the customer I retrieved before, I'd use code like this:
Adding a new row to a table is a two-step process: first you create the object and then you append it to some collection. The ObjectContext object has a generic CreateObject method that, when passed one of the entity types, returns an object of that type. Once you've created the object, you can set its properties, as this code does:
cust = en.CreateObject(Of Customer)()
cust.CustomerID = "PHVIS"
cust.CompanyName = "PH&V Information Services"
Now you can add your new object to the model. The obvious way to add a Customer object to the model is to use the AddObject method on the ObjectContext's Customers collection:
However, a Customer object also appears in the Customer property of any Order object that the customer has created. If you've just created an Order object that you need to associate with a Customer, all you need to do is set the Customer property on the Order object to the Customer object:
ord.Customer = cust
The good news is that once you've added your new object anywhere in your model, EF will "fix up" the other references. Setting the Customer property on an Order will, for instance, causes the Customer object to show up in the Customers collection.
Once you've made your changes, you need to tell EF to generate and submit the necessary SQL statements. You do that by calling the ObjectContext's SaveChanges method. That method wraps up all the updates into a transaction—either all the changes succeed or they all fail. If you want to have some of your changes succeed even if others fail, just call the SaveChanges method after each change you make to the EF objects.
You've probably noticed that using EF rather than SQL to do updates and deletes is inherently less efficient. Using SQL, you can submit a statement to update or delete a row. Using EF you must first retrieve the object before you can update or delete it—an extra trip to the database. In a part of an application that needs the fastest possible performance (and many applications have two or three of these), that might be a good reason to stick with ADO.NET. Everywhere else, though, I'm more than happy to let EF do all the work.
Peter Vogel is a system architect and principal in PH&V Information Services. PH&V provides full-stack consulting from UX design through object modeling to database design. Peter tweets about his VSM columns with the hashtag #vogelarticles. His blog posts on user experience design can be found at http://blog.learningtree.com/tag/ui/.