ADO.NET Data Services - Enforcing FK Associations and a Fix for Deleting Entities
Last post I described one way to build a smart client in WPF against ADO.NET Data Services. In this example we are editing the products table and associating them to categories in the categories table from Northwind. In the Northwind database the CategoryID on the Products table allows nulls so that you can have products that do not necessarily belong to a category. More common, however, are associations that are required and enforced by not allowing nulls in the foreign keys in the database. If we make a change to our Northwind data model to enforce this we will encounter errors when we try to save the data back to the service.
Enforcing Foreign Key Constraints
Let me show you what I mean by changing the example to enforce that products belong to a category. Open up the Northwind data model in the designer and select the FK_Products_Categories association and change the multiplicity property on the category end to 1 (One) and then save the model and build the service. Then make sure to update the service reference on the client and rebuild again (under the client project expand Service References then right-click on the reference and choose Update Reference).
If we tried to use the original code sample against this new data model we would have problems. When we run the client and make a change to a product and associate a new category we would get the error: A relationship is being added or deleted from an AssociationSet 'FK_Products_Categories'. With cardinality constraints, a corresponding 'Products' must also be added or deleted. This is a problem because the Data Services client was trying to delete the link to the category on the product first and then update the product after that.
If we tried to add a new product it would have also failed: Entities in 'NorthwindEntities.Products' participate in the 'FK_Products_Categories' relationship. 0 related 'Categories' were found. 1 'Categories' is expected. This is a problem because the client data service is attempting to add a new product with no associated category (if you look in the request header you will not see the category reference). Now that we've changed the model these situations are not allowed so they fail.
In order to fix them I had to make a couple code changes. First we need to change how we were setting the category association. Instead of calling DeleteLink and AddLink on the Category entity we need to call SetLink on the Product entity instead. In fact, this will work regardless if we are enforcing the association exist or not so it's best to use this when setting 1..1 associations (and it makes a lot more sense to me as well). Because of this I updated the previous post and the code sample on code gallery with this new code. So when we edit our Product we need to change our code to this:
Dim p As Product = CType(Me.ProductView.CurrentItem(), Product)
If p IsNot Nothing Then
Dim frm As New ProductDetail()
frm.Product = p
frm.CategoryList = Me.CategoryLookup
Dim oldCategory = p.Category
If frm.ShowDialog() Then 'changes were made
Dim newCategory = p.Category
'If the category was changed, set the new link
' then set the product state to updated
With Me.DataServiceClient
If (newCategory IsNot oldCategory) Then
.SetLink(p, "Category", newCategory)
'This will not work if we are enforcing the FK to
' Category from Product. Use SetLink instead
'.DeleteLink(oldCategory, "Products", p)
'.AddLink(newCategory, "Products", p)
End If
.UpdateObject(p)
End With
.
.
.
When we add new Products we also need to call SetLink after we add the new Product:
'Add a new Product to the List
Dim p As Product = CType(Me.ProductView.AddNew(), Product)
p.ProductName = "New Product"
'Create our detail form and setup the data
Dim frm As New ProductDetail()
frm.Product = p
frm.CategoryList = Me.CategoryLookup
If frm.ShowDialog() Then 'OK
Me.ProductView.CommitNew()
Dim newCategory = p.Category
'Add a new product and set the association to the parent Category
With Me.DataServiceClient
.AddToProducts(p)
.SetLink(p, "Category", newCategory)
'This will not work if we are enforcing the FK to
' Category from Product. Use SetLink instead
'.AddLink(newCategory, "Products", p)
End With
.
.
.
The rule of thumb is AddLink is used when we are modifying the 1...many or many...many relationships on an entity and SetLink is used to modify a 1..1 relationship as in the case of our example. Check out this blog post which should help clear up when to use SetLink versus AddLink.
Deleting Entities Involved in an Association
You may have issues deleting entities that are involved in an association (regardless of whether they are enforced or not). This was a bug in ADO.NET Data Services described in KB958484 and is fixed with this update. (This should be flowing along with your regularly scheduled Windows Updates but you may not have gotten it yet so if you're experiencing this issue make sure you install this fix.) In our example I was forced to detach the category object from the client context before submitting the deletes.
Dim p As Product = CType(Me.ProductView.CurrentItem(), Product)
If p IsNot Nothing Then
With Me.DataServiceClient
.Detach(p.Category)
.DeleteObject(p)
End With
Me.ProductView.Remove(p)
End If
Unfortunately this has side effects when working in batch like we've set up on our form. If I then added or edited another product but chose the same category that was on a product that I deleted, then I would get an InvalidOperationException on the client: The context is not currently tracking the entity. This is because I detached the category when I shouldn't have. But if I don't detach the category before deleting the product then I get another error, this time an XMLException: Root element is missing. Note, that if we didn't bring down the associated category for each product then this would work fine. It only happens when we have an association.
Once you install the update this bug will be fixed and the correct code should be to just call .DeleteObject:
With Me.DataServiceClient
.DeleteObject(p)
End With
This means that we can enable batching and add/update/delete products at will and submit them all at once in one call.
Try
'Batching will wrap all changes submitted into a transaction
Me.DataServiceClient.SaveChanges(System.Data.Services.Client.SaveChangesOptions.Batch)
MsgBox("Your data was saved")
Catch ex As Exception
MsgBox(ex.ToString())
End Try
Notice that when we batch save our changes we also get a database transaction. In your own applications you should determine if the set of data you are submitting to the server should be wrapped in a transaction or not (click image to enlarge).
Products aren't reliant on each other so in this example it's probably overkill. You should evaluate your sets of data carefully to ensure the right level of isolation is needed. I updated the sample on Code Gallery for you to play with.
You may be asking "Why doesn't the data services client do all this association linking for me automatically?" The reason I've gleaned from a few folks is that the team wanted the framework to be very simple and work with POCO (Plain Old CLR Objects) with no extra overhead. That said, there is a line of practicality here so they are looking into extending this in future versions. I'd like to also see the ability to extend the entities and add additional business functionality and properties not backed by database fields to them as well. That would make ADO.NET Data Services my data access layer of choice for general purpose n-tier smart client business applications.
Okay, so now that we have CRUD working properly in all our scenarios in the next post (soon) I'll show how we can intercept queries and change operations in order to do some additional processing -- I know I said that last post but this time I mean it ;-).
Enjoy!