Update statements genrated by Lightspeed vs Linq2Sql

Hi there, I've started to evaluate Lightspeed and have a question regarding updates. My tests are done towards the northwind test database and the productst table.

If I retrieve a product with Linq2Sql and update the productname, only that single column will be part of the update sent. Example:

UPDATE [dbo].[Products] SET [ProductName] = @p9 WHERE ([ProductID] = @p0)

If I do the same operation with lightspeed, the following update is generated:

UPDATE Products SET Discontinued = True, ProductName = 'Chef Anton\'s Gumbo Mix BAI L2S BAI', QuantityPerUnit = '36 boxes', ReorderLevel = 0, UnitPrice = 21,3500, UnitsInStock = 0, UnitsOnOrder = 0 WHERE Products.ProductID = 5

 Doesn't lightspeed support changetracking on individual properties ?
Can this be enabled in some way ?

Kind regards

Hi Ghost,

Thanks for your feedback. We do track changes at the field level internally and do have partial updates down as a potential feature to add with LightSpeed 3.0. The reason behind not adding this feature currently is for concurrency reasons.

For example, if rows are unversioned and you changed one field and somebody else changes one other it becomes possible for data in the database to be in an invalid state (we like to think at the model level more than the database so you may have a validation rule that states only one of those fields may have a value but in reality the situation could occur that data is no longer valid when selecting the object from the database).

We are considering options:

• Off by default and if you enable partiail updates then you're on your own if things get into an invalid state
• Only allow partial updates when there is a LockId field (one of LightSpeed's magic fields that if it exists it will manage it for row level versioning. Similar to CreatedDate, UpdatedDate etc)
  

I appreciate any further feedback you have on this potential feature addition. The fact you've asked for it boosts it up our internal priority list. We have had a couple of requests for this capability since we first started with LightSpeed so we'll certainly be looking more closely at it.

I hope that helps,

John-Daniel Trask

Hi John-Daniel, thanks for the reply. I do see your concern from a model perspective.

But, as a consultant, I often have to make good with what I get. Database may already exist and the customer demands certain functionality. If I am to switch to a new database access "tool" I need it to support my needs, even if they are not "best practices" or "the preferred way". If I do choose to use a tool like lightspeed, the tool has to be able to adapt to my situations and work with me.

I really like the way you have created lightspeed. The object model is clean and naming conventions seems logical to me. I find it easy to work with and that’s a huge advantage. All in all it’s a great product.

I’m digressing (something I'm really good at ;). As for the partial update case I'll try to list some thoughts:

1. Invalid state. In the case you mentioned, where one field is dependent on the other field there is a potential for invalid state. If I had mutually exclusive fields, as an example a bit field for Man and a bit field for woman (instead of gender) where only one could be set, I would ensure that the setter of "Man" would also clear the bit on "Woman", and vice versa. Doing this would make both fields dirty and a part of the update. Data would be valid and the domain validation rules would be fulfilled.
   
   But I see your point; data could potentially be in an invalid state.
   
2. The last case where I needed partial updates was a web project where the database already existed at the customer. The main table had tons of columns in it, where different groups of users would usually work on different "columns". Being a web project and stateless, that created a huge concurrency problem. Since they edited the same record all the time, they constantly got concurrency violations and handling those on the web isn’t always cool. So it became quite user unfriendly.
   
   In this case, where I couldn't remodel the database, partial updates worked like a charm. I split the input pages into logical units and they only worked on the parts of the table they needed. Concurrency issues disappeared, the users got a "data merge" instead of "last in wins" and everyone was happy :)
   
3. Working in a stateless web environment, the "unit of work" never stays alive for too long. (Unless you store it in session but I never do that for scaling and load balancing issues). If we look at a simplified scenario:
   
   a) Pageload. Object is retrieved with lightspeed and text fields updated. Html is sent to browser.
   b) User changes the Name property and hits save.
   c) Postback occurs. Since I don't store the UnitOfWork, a new one is created and the object is retrieved once more. This means that changes others users have done since the page originally loaded will be in this object.  Only Name is changed and partial update ensures minimal traffic to the server. The user also gets a merge of data instead of last in wins. Since the UnitOfWork has a very short times span, no data is "stale".

That was just some thoughts, hope they can help you in some way.
I haven't gotten around looking at your special fields yet, but will do so now. Maybe you have other mechanisms which would create the same result.

Kind regards

All that beeing said, Im an old dog set in a relational way of seeing things. Maybe I could have split up the table in logical entities / views in lightspeed and completel avoided the whole problem :)