Concurrency Control of WCF Service with Entity Framework

• Download source code - 54.8 KB

Introduction

In a previous article (WCFandEF.aspx), we implemented a simple WCF service with one operation, GetProduct. That operation accepts an integer (the product ID) as the input, and connects to a backend database to retrieve the product details for the specified product ID through Entity Framework.

In this article, we will enhance that WCF service with another operation, UpdateProduct, to update a product in the database through the Entity Framework. We will add concurrency control support to this operation, and we will create a test client to test the UpdateProduct operation with concurrency control support.

The code in this article will be based on the code we had implemented in last article. If you haven't read that article, please read it first, or you can just download the source code from that article and continue with it in this article.

In this article, we will explain and test the concurrency control of a WCF service with EF, in the following order:

• Explain WCF and EF concurrency control options
• Use regular columns
• Use a row version column
• Add a row version column to the Products table
• Modify the WCF service operation GetProduct
• Update the EF model
• Modify the Product data contract
• Modify the translate method
• Test the GetProduct operation
• Add the WCF service operation UpdateProduct
• Add the UpdateProduct operation contract
• Implement the UpdateProduct operation
• Test the UpdateProduct operation using the WCF Test Client
• Test the service using our own client
• Create our own test client
• Test the service using our own client

WCF and EF concurrency control options

There are typically three ways to handle concurrent updates to the same data by different users/processes. The first one is, the last update always wins, i.e., no concurrency control at all. The second is to lock the data before doing any actual update work, i.e., pessimistic control. The third one is to check the data version right before the actual update, and if there is a conflict, alert the user about the conflict (or resolve the conflict automatically, if it is designed so). The last one, i.e., optimistic concurrency control is the one we are going to discuss in this article.

Using a regular column to control concurrent updates

Entity Framework supports optimistic concurrency control in two ways. The first way is to use a regular data column to detect the conflicts. We can use the Concurrency Mode property for this purpose, and if this property is set to be Fixed for a column, when a change is submitted to the database, the original value and the database value of this column will be compared. If they are different, a conflict is detected.

With this mechanism, only the involved column is protected for concurrent updates. All other columns can still be updated by multiple users / processes simultaneously without causing conflicts.

Using a row version column to control concurrent updates

The second and a more efficient way that provides conflict control is by using a version column. If you add a column of type Timestamp, or ROWVERSION, to a database table, then when you add this table to the entity model, this column will be marked as a concurrency control version property.

Version numbers are incremented, and timestamp columns are updated every time the associated row is updated. Before the update, if there is a column of this type, EF will first check this column to make sure that this record has not been updated by any other user/process. This column will also be synchronized immediately after the data row is updated. The new values are visible after SaveChanges finishes.

In this article, we will use the row version mechanism to control concurrent updates.

Adding a version column to the database table

First, we need to add a new column called RowVersion, which is of type Timestamp, to the Products table in the database. In this article, we will use the same Northwind database that we have used in my previous article. Please refer to my previous article to find out more about setting up the database environment.

After you have the database setup, you can add the new column within SQL Server Management Studio, as shown in the following image:

Modifying the WCF service GetProduct operation

Now that we have the database ready, we need to modify the service to use this new column to control concurrent updates. In this section, we will refresh the EF model to add the new column, modify the data contract to include this new column, modify the translate methods to translate this new column, and of course, test the GetProduct method to return this new column.

Updating the EF model for the row version column

First, we need to refresh our EF data model to take this new column to the data model. Follow these steps to refresh the data model:

1. Open the solution WCFandEFSolution. You can download the source code of this solution from my previous article if you haven't done so.
2. From Visual Studio, open the Northwind.edmx entity designer, right click on an empty space, and select Update Model from Database... Click the Refresh tab, and you will see Products in the refresh list.
3. Click Finish.

Now a new property RowVersion has been added to the Product entity in the Northwind.edmx data model. However, its Concurrency Mode is set to None now, so you need to change it to be Fixed.

To do so, you can click the RowVersion property from the ProductEntity in the Northwind entity model, then from the Properties window, change its Concurrency Mode from None to Fixed, like in this picture:

Note that its StoreGeneratedPattern is set to Computed, and this is to make sure this property will be refreshed every time after an update.

Modifying the Product data contract

Now we have the new column in database and in the data model; next, we need to modify our data contract to include this new column.

We need to add a new property to the data contract class to hold the RowVersion value. To do this, open the IProductService.cs file in the project and add this property to the class:

[DataMember] public Byte[] RowVersion { get; set; } 

The data contract class should be like this now:

[DataContract]
public class Product
{
    [DataMember]
    public int ProductID { get; set; }
    
    [DataMember]
    public string ProductName { get; set; }
    
    [DataMember]
    public string QuantityPerUnit { get; set; }
    
    [DataMember]
    public decimal UnitPrice { get; set; }
    
    [DataMember]
    public bool Discontinued { get; set; }
    
    [DataMember]
    public Byte[] RowVersion { get; set; }
}

Modifying the translate method to translate the RowVersion column

In the previous article, we created a translating method to translate from a ProductEntity type object to a Product type object. Now that we have a new column RowVersion, we need to add the following line of code to this translate method:

product.RowVersion = productEntity.RowVersion;

So the method should be like this now:

private Product TranslateProductEntityToProduct(
ProductEntity productEntity)
{
    Product product = new Product();
    product.ProductID = productEntity.ProductID;
    product.ProductName = productEntity.ProductName;
    product.QuantityPerUnit = productEntity.QuantityPerUnit;
    product.UnitPrice = (decimal)productEntity.UnitPrice;
    product.Discontinued = productEntity.Discontinued;
    product.RowVersion = productEntity.RowVersion;
    return product;
}

Testing the GetProduct operation with the new column

Now that the new column is in the database, in the data contract, and we have also added code to translate from the Entity type to the data contract type, we should test it before trying to add the UpdateProduct operation. Just press Ctrl+F5 to start the application, double click the GetProduct operation from the left panel, enter a valid product ID, then click the Invoke button. Instead of getting the product details back from the database, you may get this error message:

This is because we have changed the data contract, but the WCF Test Client is still using the cached proxy. To solve this problem, check "Start a new proxy", and click the Invoke button again. This time, you should get a screen like this:

From this image, we know the product RowVersion is returned from the database to the client. It is of Byte[] type.

Adding the WCF service operation UpdateProduct

In the previous section, we successfully added a row version column, changed the data contract, and tested it with the existing GetProduct method. Now in this section, we are going to add a new operation, UpdateProduct, to actually demonstrate the concurrency control of EF within a WCF service.

Adding the UpdateProduct operation contract

First, we need to add the operation contract to the service interface. Follow these steps:

1. Open file IProductService.cs file.
2. Add the following code to the interface IProductService:

[OperationContract] 
bool UpdateProduct(ref Product product); 

The interface definition is like this now:

[ServiceContract]
public interface IProductService
{
    [OperationContract]
    Product GetProduct(int id);
    [OperationContract]
    bool UpdateProduct(ref Product product);
}

Note: we have passed in the product parameter as a ref parameter. The purpose of this ref is to make sure we can get the modified RowVersion back so we can update products continuously, if we want to do so.

Implementing the UpdateProduct operation

Now we need to implement the UpdateProduct method. Open the Product.cs file, and add the following method to the Product class:

public bool UpdateProduct(ref Product product)
{
    // check product ID
    NorthwindEntities context = new NorthwindEntities();
    int productID = product.ProductID;
    ProductEntity productInDB =
    (from p
    in context.ProductEntities
     where p.ProductID == productID
     select p).FirstOrDefault();
    // check product
    if (productInDB == null)
    {
        throw new Exception("No product with ID " + product.ProductID);
    }
    // detach it first
    context.Detach(productInDB);
    // update the product
    productInDB.ProductName = product.ProductName;
    productInDB.QuantityPerUnit = product.QuantityPerUnit;
    productInDB.UnitPrice = product.UnitPrice;
    productInDB.Discontinued = product.Discontinued;
    productInDB.RowVersion = product.RowVersion;
    // attach it
    context.Attach(productInDB);
    // change object state
    context.ObjectStateManager.ChangeObjectState(
            productInDB, System.Data.EntityState.Modified);
    context.SaveChanges();
    product.RowVersion = productInDB.RowVersion;
    context.Dispose();
    return true;
}

A few notes for the above code:

1. You have to save the product ID in a new variable, then use it in the LINQ query. Otherwise, you will get an error saying "Cannot use ref or out parameter 'product' inside an anonymous method, lambda expression, or query expression".
2. If Detach and Attach are not called, the RowVersion from the database, not from the client, will be used when submitting to the database, even though you have updated its value before submitting to the database. As a result, the update will always succeed but without concurrency control.
3. If Detach is not called, when you call the Attach method, you will get an error "The object cannot be attached because it is already in the object context".
4. If ChangeObjectState is not called, Entity Framework will not honor your change to the entity object and you will not be able to save any changes to the database.
5. In EF4, there are new ways to handle updating detached entities. You should do your own investigation if you want to take advantage of those new mechanisms.

Also, here for the update, we didn't add a new translate method to translate the Product type object to a ProductEntity type object. This is because we have only one layer in this simple service, so we didn't bother to do so. In an enterprise WCF service, you may have three layers in your service, i.e., interface layer, business logic layer, and data access layer. In that case, you ought to add a new translate method, so down below the interface layer, you only deal with the ProductEntity object.

Same as we did in our last article, we didn't expose the entity object to the client in this article. I think this is a better practice as this will make the service interface independent of the underlying database. It may seem tedious to map the underlying entity objects to the service contract objects, but in your real projects, if you take advantage of some code generation tools, this will not be a burden at all. You can search the internet on this topic to get more ideas and make your own decision.

Testing the UpdateProduct operation with the WCF Test Client

Now that we have concurrency support added to the service, let's test it with the built-in WCF Test Client.

Press Ctrl F5 to start the program. Click UpdateProduct, enter a valid product ID, e.g., 5, a new name, quantity per unit, and unit price. However, you can't enter a value to the RowVersion field for this update because it is of byte[] type.

If you click the Invoke button to call the service, you will get an exception like this:

From this image, we know the update failed. If you debug the service, or turn on the option IncludeExceptionDetailInFaults, you will find out it is due to a concurrency exception. The reason is the built-in WCF Test Client didn't/couldn't pass in the original RowVersion for the object to be updated, and the Entity Framework thinks this product has been updated by some other user.

Testing the UpdateProduct operation with our own client

As we have seen, we can't test the concurrency control with the built-in WCF Test Client. Next, we will create a WinForms client to test this.

Creating the test client

In this section, we will create a WinForms client to get the product details and update the price of a product.

Follow these steps to create the test client:

1. In Solution Explorer, right click the solution item, and select Add | New Project...
2. Select Visual C# | Windows Forms Application as the template, and change the name to TestClient. Click OK to add the new project.
3. On the form designer, add the following five controls:
• A Label, named lblProductID, with Text: Product ID
• A TextBox, named txtProductID
• A Button, named btnGetProduct, with Text: &Get Product Details
• A Label, named lblProductDetails, with Text: Product Details
• A TextBox, named txtProductDetails, with Multiline property set to True

The layout of the form look like this:



4. In the Solution Explorer, right click on the TestClient project, select Add Service Reference...
5. On the Add Service Reference window, click Discover, wait a minute until the service is displayed, then change the Namespace from ServiceReference1 to ProductServiceRef, and click OK.

The Add Service Reference window should be like this:

Implementing the GetProduct functionality

Now that we have the test client created, we will customize the client application to test the new WCF service. First, we would need to customize the test client to call the WCF service to get a product from the database so that we can test the GetProduct operation with LINQ to Entities.

We will call a WCF service through the proxy, so let's add the following using statements to the form class in the file Form1.cs:

using TestClient.ProductServiceRef;
using System.ServiceModel;

Then on the Forms Designer, double click btnGetProductDetails, and add an event handler for this button as follows:

private void btnGetProduct_Click(object sender, EventArgs e)
{
    ProductServiceClient client = new ProductServiceClient();
    string result = "";
    try
    {
        int productID = Int32.Parse(txtProductID.Text.ToString());
        Product product = client.GetProduct(productID);
        StringBuilder sb = new StringBuilder();
        sb.Append("ProductID:" + product.ProductID.ToString() + "\r\n");
        sb.Append("ProductName:" + product.ProductName + "\r\n");
        sb.Append("QuantityPerUnit:" + product.QuantityPerUnit + "\r\n");
        sb.Append("UnitPrice:" + product.UnitPrice.ToString() + "\r\n");
        sb.Append("Discontinued:" + product.Discontinued.ToString() + "\r\n");
        sb.Append("RowVersion:");
        foreach (var x in product.RowVersion.AsEnumerable())
        {
            sb.Append(x.ToString());
            sb.Append(" ");
        }
        result = sb.ToString();
    }
    catch (TimeoutException ex)
    {
        result = "The service operation timed out. " +
        ex.Message;
    }
    catch (FaultException ex)
    {
        result = "Fault: " +
        ex.ToString();
    }
    catch (CommunicationException ex)
    {
        result = "There was a communication problem. " +
        ex.Message + ex.StackTrace;
    }
    catch (Exception ex)
    {
        result = "Other excpetion: " +
        ex.Message + ex.StackTrace;
    }
    txtProductDetails.Text = result;
}

Implementing the UpdateProduct functionality

Next, we need to modify the client program to call the UpdateProduct operation of the Web Service. This method is particularly important to us because we will use this method to test the concurrent update control of Entity Framework.

First, we need to add some more controls to the form. We will modify the form UI as follows:

1. Open the file Form1.cs in the TestClient project.
2. Add a Label named lblNewPrice with text New Price.
3. Add a TextBox named txtNewPrice.
4. Add a Button named btnUpdatePrice with text &Update Price.
5. Add a Label named lblUpdateResult with text Update Result.
6. Add a TextBox named txtUpdateResult, with Multiline property set to True and Scrollbars set to Both.

The form should now appear as in the following screenshot:

Now, double-click the Update Price button, and add the following event handler method for this button:

private void btnUpdatePrice_Click(object sender, EventArgs e)
{
    string result = "";
    if (product != null)
    {
        try
        {
            // update its price
            product.UnitPrice =
            Decimal.Parse(txtNewPrice.Text.ToString());
            ProductServiceClient client = new ProductServiceClient();
            StringBuilder sb = new StringBuilder();
            sb.Append("Price updated to ");
            sb.Append(txtNewPrice.Text.ToString());
            sb.Append("\r\n");
            sb.Append("Update result:");
            sb.Append(client.UpdateProduct(ref product).ToString());
            sb.Append("\r\n");
            sb.Append("New RowVersion:");
            foreach (var x in product.RowVersion.AsEnumerable())
            {
                sb.Append(x.ToString());
                sb.Append(" ");
            }
            result = sb.ToString();
        }
        catch (TimeoutException ex)
        {
            result = "The service operation timed out. " + ex.Message;
        }
        catch (FaultException ex)
        {
            result = "Fault: " + ex.ToString();
        }
        catch (CommunicationException ex)
        {
            result = "There was a communication problem. " + 
                     ex.Message + ex.StackTrace;
        }
        catch (Exception ex)
        {
            result = "Other excpetion: " + ex.Message + ex.StackTrace;
        }
    }
    else
    {
        result = "Get product details first";
    }
    txtUpdateResult.Text = result;
}

Note: inside the Update Price button event handler listed above, we don't get the product from the database first. Instead, we reuse the same product object from the btnGetProduct_Click method, which means we will update whatever product we get when we click the button Get Product Details. In order to do this, we need to move the product variable outside of the private method btnGetProduct_Click, to be a class variable like this:

Product product;

And inside the btnGetProduct_Click method, we should not define another variable product, but use the class member product now. The first few lines of code for class Form1 should be like this now:

public partial class Form1 : Form
{
    Product product;

    public Form1()
    {
        InitializeComponent();
    }

    private void btnGetProduct_Click(object sender, EventArgs e)
    {
        ProductServiceClient client = new ProductServiceClient();
        string result = "";

        try
        {
            int productID = Int32.Parse(txtProductID.Text.ToString());
            product = client.GetProduct(productID);
            // More code to follow

Also, as you can see, we didn't do anything specific about the concurrent update control of the update, but later, in the section "Testing concurrent update" within this article, we will see how the Entity Framework inside the WCF service handles this for us.

Also, here we will capture all kinds of exceptions and display the appropriate messages for them.

Testing the GetProduct and UpdateProduct operations

We can build and run the program to test the GetProduct and UpdateProduct operations now. Because we are still using the WCF Service Host to host our service, we need to start it first.

1. Make sure the project WCFandEFService is still the startup project, and press Ctrl+F5 to start it. The WCF Test Client will also be started. Don't close it; otherwise, the WCF Service Host will be closed and you will not be able to run the client application.
2. Change the project TestClient as the startup project, and press Ctrl+F5 to start it.
3. Alternatively, you can set the solution to start up with multiple projects, with the project WCFandEFService to be started up first, then the project TestClient to be started up next. In some cases, you may have to do this, because sometimes as soon as you press Ctrl+F5 to start the client project, the WCF Service Host (and the WCF Test Client) will be closed automatically, making the client not able to connect to the service. You may also start the service first, then start the client from Windows Explorer by double clicking the executable file of the client application.
4. On the Client form UI, enter 10 as the product ID in the Product ID text box, and click the button Get Product Details to get the product details. Note that the unit price is now 31.0000, and the RowVersion is 0 0 0 0 0 0 12 40, as in the following screenshot:



5. Now enter 32 as the product price in the New Price text box, and click the Update Price button to update its price. The Update Result should be True. Note: the RowVersion has been changed to 0 0 0 0 0 0 35 145.



6. To verify the new price, click the Get Product Details button again to get the product details for this product, and you will see that the unit price has been updated to 32.0000.

Testing concurrent update

We can also test concurrent updates by using this client application TestClient.

In this section, we will start two clients and update the same product from these two clients at the same time. We will create a conflict between the updates from these two clients so we can test if this conflict is properly handled by the Entity Framework.

The test sequence will be like this:

1. First client starts.
2. Second client starts.
3. First client reads the product information.
4. Second client reads the same product information.
5. Second client updates the product successfully.
6. First client tries to update the product, and fails.

The last step is where the conflict occurs, as the product has been updated in between the read and the updated by the first client.

The steps are described in detail below:

1. Start the WCF Service Host application in non-debugging mode, if you have stopped it (you have to set WCFandEFService as the startup project first).
2. Start the client application in non-debugging mode by pressing Control+F5 (you have to set TestClient as the startup project). We will refer to this client as the first client. As we said in the previous section, you have options to start the WCF service and the client applications at the same time.
3. In this first client application, enter 10 in the Product ID text box, and click the Get Product Details button to get the product's details. Note that the unit price is 32.0000 and the RowVersion is 0 0 0 0 0 0 35 145.
4. Start another client application in non-debugging mode by pressing Control+F5. We will refer to this client as the second client.
5. In the second client application, enter 10 in the Product ID text box, and click the Get Product Details button to get the product's details. Note that the unit price is still 32.0000 and the RowVersion is 0 0 0 0 0 0 35 145. Actually, the second client form window should be identical to the first client form window.
6. On the second client form UI, enter 33 as the product price in the New Price text box, and click the Update Price button to update its price.
7. The second client update is committed to the database, and the Update Result value is True. The price of this product has now been updated to 33, and the RowVersion has been updated to a new value 0 0 0 0 0 0 35 146.
8. In the second client, click the Get Product Details button to get the product details to verify the update. Note that the unit price is now 33.0000, and RowVersion is now 0 0 0 0 0 0 35 146.
9. On the first client form UI, enter 34 as the product price in the New Price text box, and click the Update Price button to update its price.
10. The first client update fails.
11. In the second client, click Get Product Details again to get the product's details. You will see that the unit price is still 33.0000 and the RowVersion is still 0 0 0 0 0 0 35 146, which means that the first client's update didn't get committed to the database.

The following image is for the first client. You can see the update failed with an exception.

From the test above, we know that the concurrent update is controlled by LINQ to Entities. An optimistic locking mechanism is enforced, and one client's update won't overwrite another client's update. The client that has a conflict will be notified by a fault message.

Note: concurrent update locking is applied at the record level in the database. If two clients try to update different records in the database, they will not interfere with each other. For example, if you repeat the above steps to update product 10 in one client and product 11 in another client, there will be no problem at all.

Summary

In this article, we have enhanced the simple WCF service that we have created in a previous article to support concurrent updates. The key points in this article include:

• Entity Framework can handle concurrent updates by comparing all values or utilizing a version column
• Entity Framework data model can be refreshed at any time to add new tables, or columns
• The built-in WCF Test Client can test simple WCF services, but not complex WCF services
• The new row version column value can be returned to the client from Entity Framework after an update
• The entity object can be exposed to the client but it is preferred not to do so

Note: this article is based on chapter 8 and chapter 9 of my book "WCF 4.0 Multi-tier Services Development with LINQ to Entities" (ISBN 1849681147). This book is a hands-on guide to learn how to build SOA applications on the Microsoft platform using WCF and LINQ to Entities. It is updated for VS2010 from my previous book: WCF Multi-tier Services Development with LINQ.

With this book, you can learn how to master WCF and LINQ to Entities concepts by completing practical examples and applying them to your real-world assignments. This is the first and only book to combine WCF and LINQ to Entities in a multi-tier real-world WCF Service. It is ideal for beginners who want to learn how to build scalable, powerful, easy-to-maintain WCF Services. This book is rich with example code, clear explanations, interesting examples, and practical advice. It is a truly hands-on book for C++ and C# developers.

You don't need to have any experience in WCF or LINQ to Entities to read this book. Detailed instructions and precise screenshots will guide you through the whole process of exploring the new worlds of WCF and LINQ to Entities. This book is distinguished from other WCF and LINQ to Entities books by that, this book focuses on how to do it, not why to do it in such a way, so you won't be overwhelmed by tons of information about WCF and LINQ to Entities. Once you have finished this book, you will be proud that you have been working with WCF and LINQ to Entities in the most straightforward way.

You can buy this book from Amazon, or from the publisher's website at https://www.packtpub.com/wcf-4-0-multi-tier-services-development-with-linq-to-entities/book.