3. Updates, Inserts, and Deletes
The Entity Framework isn't
limited to querying data. You can also perform inserts, updates, and
deletes. The best part is that you accomplish all this by manipulating
the familiar entity objects. Once again, the Entity Framework translates
your VB code into the appropriate SQL database commands. All you need
to do is call the SaveChanges() method of the context object. In the
current example, that means a quick call to
NorthwindEntities.SaveChanges() will commit any recent changes to the
database.
Figure 2
shows a web page that puts this into practice. It includes a grid of
products and three buttons: one that changes the currently selected
record, one that inserts a new record, and one that deletes the selected
record.
To update a record, you
need to first retrieve the right entity object. The technique you use
depends on the exact task you're performing, but you might get your
object by iterating through a collection, following a navigation
property, or performing a LINQ expression, as shown here.
However, there's one minor
trick. All LINQ expressions return an iterator that allows you to
traverse a collection of objects. If you have an expression that you
know matches just one record (for example, one that uses the Where
keyword on a unique key value, such as ProductID), you can use the
Single() method. This executes the query and returns the one and only
matching object.
The following code selects a
specific product (using a LINQ expression), updates the stock numbers,
and saves the change. The two code statements in bold trigger immediate
database operations and so require exception handling:
Protected Sub cmdUpdate_Click(ByVal sender As Object, ByVal e As EventArgs) _
Handles cmdUpdate.Click
' Only allow updating if a record is currently selected.
If gridProducts.SelectedIndex <> −1 Then
' Use a LINQ expression to find the selected product.
Dim selectedProductID As Integer = Val(gridProducts.SelectedDataKey.Value)
Dim matches = From p In entities.Products
Where p.ProductID = selectedProductID
Select p
' Execute the query and return the entity object.
Dim product As Product = matches.Single()
' Change the entity object.
product.UnitsInStock -= 1
' Commit the changes back to the database.
entities.SaveChanges()
End If
End Sub
NOTE
SaveChanges() also
returns an integer that indicates how many records were updated. So if
you get a result of 0, you know that no operations were performed.
Inserting a new record is
nearly as easy. The best approach is to use the shared factory method of
the appropriate entity class. So if you want to create a new product,
call Product.CreateProduct(). Then, add the new object to the
NorthwindEntities.Products collection using the AddObject() method, and
commit the update with SaveChanges():
Protected Sub cmdInsert_Click(ByVal sender As Object, ByVal e As EventArgs) _
Handles cmdUpdate.Click
' The CreateProduct() method requires the three non-nullable Product fields
' as arguments: ProductID, ProductName, and Discontinued. However, the
' ProductName isn't actually used--when the update is finished, it's replaced
' by the automatically generated ID that the database creates.
Dim newProduct As Product
newProduct = Product.CreateProduct(0, "Thick-As-Cement Milkshake", False)
' You can now set additional properties that aren't required.
newProduct.CategoryID = 1
newProduct.UnitsInStock = 10
newProduct.UnitPrice = 15.99D
' Finally, commit the changes and insert the record in the database.
entities.Products.AddObject(newProduct)
entities.SaveChanges()
End Sub
Finally, a delete
operation is the simplest data operation of all. You simply need to call
the DeleteObject() method of the appropriate collection, followed by
SaveChanges(). The following code retrieves the currently selected
product object and removes it from the database using this approach:
Protected Sub cmdDelete_Click(ByVal sender As Object, ByVal e As EventArgs) _
Handles cmdDelete.Click
' Check if a row is selected.
If gridProducts.SelectedIndex <> −1 Then
' Use a LINQ expression to find the selected product.
Dim selectedProductID As Integer = Val(gridProducts.SelectedDataKey.Value)
Dim matches = From p In entities.Products
Where p.ProductID = selectedProductID
Select p
' Execute the query and return the entity object.
Dim product As Product = matches.Single()
' Delete the record from the database.
entities.Products.DeleteObject(product)
entities.SaveChanges()
' Clear the selection (which may now be pointing to a different row.)
gridProducts.SelectedIndex = -1
End If
End Sub
The Entity Framework uses a fairly sophisticated change tracking system to make these operations work.
When you call
SaveChanges(), the context object attempts to commit all of the changes
in the data that it's tracking, including updates, deletions, and
insertions. If you have related records, the Entity Framework will apply
the changes in an order that makes sense (for example, it won't attempt
to insert a child record before inserting a linked parent). This
ability is more interesting if you use the Entity Framework in a rich
desktop application, where you might keep the entity objects in memory
for a long period of time, and periodically commit a batch of updates.
But in a web application, where your page lives for only a few seconds
(or less), you'll almost always create a new context object, perform all
your database operations immediately, and then discard it.
NOTE
When you call
SaveChanges(), all the changes are performed as part of a single
transaction. If an error occurs, the entire process is rolled back, but
your objects remain in their current state, with any new and edited
information. That means you can programmatically resolve the problem and
call SaveChanges() again.
4. Managing Concurrency
Update errors can be caused
by all the usual factors—a timeout waiting for a connection, a network
error, and so on—but the most common update error is a concurrency
problem.
There are two overall strategies
you can use to deal with concurrency issues: you can try to avoid
concurrency problems before they happen, or you can try to resolve
concurrency issues after they occur.
By default, the Entity
Framework uses the "last-in-wins" updating strategy. This means that new
updates always succeed, but they may wipe out another user's changes.
However, this doesn't pose the same problem that it does with the
SqlDataSource control .
That's because the Entity Framework doesn't apply old values. If you
change a single field and call SaveChanges(), the Entity Framework uses
an update command that modifies just that field.
The Entity Framework's
updating strategy can still cause a problem if two users are updating
the same record at once and they are editing the same fields (or, if the
changes they're making to different fields aren't consistent).
Depending on the way you're using the database and the frequency of
edits, this may not be a significant concern for your application.
(After all, the time taken to make and commit an edit in an ASP.NET
application is very small, unlike a desktop application.) But if you
need to catch potential problems that involve overlapping edits, you can
configure the Entity Framework to use stricter concurrency checking.
Here's how it works. Using
the data model designer, you choose the fields that must match in order
for an update to succeed. For example, you might decide that a Customer
record update should be allowed to continue only if the ContactName and
CompanyName fields haven't been changed by another user since the record
was retrieved. To do this, start by clicking the ContactName field in
the Customer table box. Then, change the Concurrency Mode property in
the Properties window from None to Fixed. Finally, repeat this process
for the CompanyName field.
Now, if you attempt to
commit a change and it overlaps with records that another user has
changed, the edit will fail with an OptimisticConcurrencyException. At
this point, you can try to resolve the problem or give the user the
choice of reapplying the update.
