Saving only updated parts of an entity?

I noticed when I was doing testing with Lightspeed that the SQL generated for an update will include ALL of the properties in the entity getting saved to the database, if you just changed ONE of the properties. This is totally not going to work for us, because we need to be able to save just the values that have changed in an entity to the database as some entities, like the Product entity, have important fields like the Qty in stock that should NEVER be touched unless we are explicity updating it (and when we do that, we use Mutexes to ensure the values are consistent).

So when I save a Product entity and I have modified something unrelated like the weight, we cannot have it saving all the data.

Is there a way to get Lightspeed to do partial updates on the entity for only the values that changed, like other ORM's do?

Hello Kendall,

LightSpeed does this if there is concurrency checking in place. This involves adding a field to your table:

http://www.mindscapehq.com/documentation/lightspeed/Implementing-Storage-Policies-with-LightSpeed/Concurrent-Editing

Once it place LightSpeed will only update what is required. This saves on needing to send all fields down the line checking that all other fields match the values we expect. 

Once enabled, partial updates occur.

Most other ORM's will do this based on what fields have actually changed in the entity, since they either do change tracking on the entity properties, or they have the original values stored and compare those on save (Entity Framework DbContext). Generally I have found change tracking to be much faster when saving the entities in EF making the DbContext quite a bit slower for saving decent sized graphs of entities.

But the problem is I need to be able to do this without having to include a concurrency field in the table. In fact the mechanism you propose simply won't work for us either, because if someone purchases a product, the LockVersion field will get updated because the Qty field will have been decremented. Then if one of my copyrighting guys changes something (maybe the description, or fixes the weight or something) and from the time he opened the page to the time he submitted his changes, an order got placed, we now have a concurrency exception even though none of the fields changed actually conflicted!

A much better way to handle concurrency exceptions is to not use a LockVersion field at all, and simply include all the original values for the entity in the where clause, and ONLY include in the update code (and in the where clause) the fields that actually changed. Check the return value from the DB indicating how many rows were affected, and if that comes back as 0, you had a conflict. And since you are only touching fields that actually changed, two updates can occur in parallel and as long as they don't touch any of the same fields, you have no concurrency conflict.

I can do some of my own support for this at a higher level, which is what I was planning to do, but if Lightspeed is always going to save ALL the entity members on updates, I have no way to implement this.

I understand that for some simpler tables where concurrency control is not important you can just use a simple 'last in wins' approach, the overhead of determining what has actually changes may be higher than simply stuffing all the values into the database as an update. So that approach makes sense, but it won't work for many of the important entities in our database.

Thanks for the feedback, in particular the suggestion about supporting partial updates outside of using the LockVersion is something we think would be quite useful to look at adding in. As you have pointed out, partial updates do require you to be using the LockVersion field so thats a hard restriction for now.

To support this we would need to make use of the Change Tracking in LightSpeed since we would need to be able to issue WHERE UpdatedField =  'OldValue' as part of the query and throw as with LockVersion if the changes could not be persisted because of a concurrency issue.

Ill add this to our backlog and hopefully we will be able to look at implementing this in the near future.

Jeremy

Ok great. There should be two levels to this support though. There should just be simple support where you can save an entity and it will only update the columns that were changed, as that is quite useful in some situations where you don't need or want the full concurrency (ie: still last-in-wins, but only for the data that actually changed).

Then the support for full concurrency that will throw an exception if something changed by setting the original values in the where clause.

Hi Kendall,

Tonights nightly will include support for this. The support is only in the core currently so you won't be able to set it up in the designer. To test it out, create a partial class for a LightSpeed entity and attribute it with:

[UnversionedPartialUpdate]

That will tell LightSpeed to use partial updates and check that the field values in the database match what you started with. If they don't you'll get a concurrency error. You should see updates following that like this:

UPDATE
    Penguins
SET
    Age = 213
WHERE
    Penguins.Id = 2 AND
    Penguins.Age = 8

Where Penguins also has fields for Name and Height but where only Age was updated from 8 to 213 and hence only Age is included.

I hope that helps - let us know how you get on.

John-Daniel Trask

Ok, that is awesome. But I also need the ability to do partial updates for all my entities without the concurrency (still last in wins, but with less chance of a conflict). So only the modified columns get updated, but there is no where clause. Many of our entities can be saved that way, and we can avoid the overhead of dealing with a concurrency exception.

Perhaps that can be an option that is global to the entire context, and the UnversionedPartialUpdate support can be enabled per entity? I assume designer support for this could be added once it is all working?