Practical .NET

Implementing Updates Without Query

The CQRS pattern separates updates from queries, allowing you to create the best process for each of those activities. Effectively, CQRS codifies the standard practice for ASP.NET MVC developers: Here are some strategies for implementing that pattern and improving your application's performance.

• By Peter Vogel
• 06/06/2016

In an earlier column, I discussed the difficulties of having one system that handles both queries and updates. The Command Query Responsibility Separation (CQRS) says that the simplest solution is to create two separate processes: one for queries (discussed in that earlier column) and one for updates (that's this column). If you carry this separation to its conclusion, you could have one database that supports retrievals while having another database to which updates are applied.

In many ways, the CQRS pattern describes what ASP.NET MVC developers create by default: one process to create a View (queries) and another process to update the data returned from the page in the browser (commands). At its most basic, CQRS boils down to performing updates independently of whatever data you retrieved to create the View. And, of course, avoiding retrievals improves performance by reducing trips to the database.

As I also suggested in that previous column, there are two kinds of updates: ones that must be done immediately, and others that can be deferred. This column addresses the immediate updates: What do you do with the user's data as soon as it's returned from the UI? In a later column I'll look at a way to make deferred updates equally as simple.

For this column, I'm going to assume that you're working in an ASP.NET MVC application with updates performed by calling Web services using the jQuery .Ajax function (but I'll also point you to solutions for posting data back to the server for processing).

With Entity Framework (EF), deletes and inserts without queries are easy to handle. To add a new row to a table, I create the corresponding entity object, set all of its properties with data from the UI, add the object to the appropriate collection on the DbContext object, and call SaveChanges. To delete a row, I create the corresponding entity object, set just those properties that correspond to the primary key columns in the table, attach the object to the appropriate collection, then remove it and call SaveChanges (I have a tip this month that shows the code).

The Cost of Updates
It's lucky that inserts and deletes are easy because updates are … challenging. The default processing for updates is to pull all the data from the UI, get the corresponding object through EF, update the values in the retrieved object's properties with the data from the UI, and call SaveChanges. Using this process, Entity Framework will generate a SQL Update statement that only updates those columns in the row whose properties were actually changed. This is a good thing: If two users retrieve the same row at the same time, with one user changing Column1 and the other changing Column2, the two users won't overwrite each other's changes. There are costs to this, though: You've made an extra trip to the database to fetch the corresponding version of the object and tied your update process to a retrieval process.

You can still perform the update without doing that retrieval, but if you're not careful, you'll just substitute one cost for another. You could, for example, create a new instance of the object and then set all of its empty properties to the data from the UI. With that process EF will generate an Update statement that updates every column in the table because, as far as EF can tell, every property on the object was changed from nothing to something. The cost here is that, if two users retrieve a row at the same time, the last user who saves will wipe out changes made by the first user, even if the two users changed different columns.

That particular problem isn't insurmountable. One solution is to use EF concurrency options so that EF throws an exception when one user tries to overwrite another user's changes. Again, you're just substituting costs: Users who have their updates rejected will have to redo their changes. If that happens frequently, you'll have unhappy users (and, of course, exceptions are inherently expensive in the .NET Framework).

Avoiding Update Costs
A third solution that avoids all of these costs is to keep track of what data the user actually changes in the UI -- and the best place to do that is on the user's computer.

In an earlier column, I designed an HtmlHelper that, in an ASP.NET MVC view, generated input tags that include the original value of the data in an attribute called data-originalvalue. A typical tag looks like this:

<input data-originalvalue="Peter" id="FirstName" name="FirstName" type="text" value="Peter" /> 

Now, when building the object to be returned to the server for processing, you can use jQuery to build the object using only data that's been changed in the UI. The following code adds properties to a cust object using names and values from the input elements, but only if the data has changed. (One exception: This code always sets the property that corresponds to the table's primary key values.) The code finishes by sending the object back to a Web Service for processing in an AJAX call:

var cust = {};
$("input[data-originalvalue]").each(function (pos, elm) {
  if (elm.attributes["data-originalvalue"].value != elm.value)
  {
    cust[elm.name] = elm.value;
  }
cust.custId = $("#custId").val();
});
$.post("/home/update",cust);

If you want to post the data back to the server when the user clicks the submit button then you can shove this object into a hidden element.

In the object coming back to the server the properties not set in the UI will be set to null/Nothing which means you can update all of the property values in the entity object you've created. Because the entity object's properties are already set to null/nothing, setting those properties to null/nothing is a "no change change" and is ignored by EF. The server-side code is very simple:

Function Update(dataUI As Customer) As ActionResult
  Dim cust As New Customer
  db.Customers.Attach(cust)
  cust.CustId = dataUI.custId
  cust.FirstName = dataUI.FirstName
  cust.LastName = dataUI.LastName
  ...rest of the entity objects/properties to be updated...
  db.SaveChanges()
  Return Json(cust, JsonRequestBehavior.AllowGet)
End Function

Now, when you call SaveChanges, only the fields set to an actual value (that is, only the values actually changed in the UI) will be updated in the database. You've got the best Update statement, skipped a trip to the database and transferred some processing to the user's computer. What's not to like?

Regardless of how you choose to address the problem, though, my message is the same: Your update process can be completely divorced from your query process. The obvious payoff is that you can now enhance/modify/evolve your data retrieval process and your update process independently of each other -- you don't have to distort one process to match the other. The real payoff is that the code in both processes gets simpler.