Magiq: Updating associations in batch challenge result
In a the post Magiq: Updating associations in batch challenge I tried to solve an issue with mssql. Basically, I wanted to update 2 tables in the same query, because the update itself changes some columns used in the where clause. Greatfully, it had several answers that led me to a solution:
There are two kind of property sets: the ones that invalidates the where clause and whe ones that doesn’t. Since there’s a way to find out if a property expression is used in another expression (visiting the expression tree or just manipulating strings), it’s easy to mark them. Magiq should execute first the ones that doesn’t invalidate the where clause. For the invalidating ones, I will use a table variable but if there’s only one of them, then it will execute a single update (because there’s nothing else to update). I’m not worried about using table variables, because in other databases the feature is fully supported.
Thanks to everyone that commented yesterday, it was very helpful!
Leave a comment
Support NHibernate
Cherry pie & damn good coffee 
If a sql table has triggers, maybe a trigger over a column that doesn’t invalidate the where-clause performs an update over a field that does invalidate.
Another problem is when you have editable views: An autocalculated column can be altered without any explicit modification.
If you continue in that way you can add some kind of workaround to force the temporal table creation.
If it’s handled always using a temporal table, in the 99% it will be useless and no performant. If you have the application object oriented, then why you do stuff in the database? If you have a good answer to that (I mean, you have a scenary when it’s needed), then you should be aware of that and know what you are doing. You have the same problem if you change the object directly, because when you saved the instance there’s a value in the database different than the one in the object.
Mmm… No, I’m saying something like:
contacts.Where( x => x.FirstName == “Ivo”)
.Set( x => x.FirstName, “IvoWiblo”)
.Set( x => x.City.HasPeopleNamedIvo, true)
.EnsureSideEffectsIndependency()
.Update();
Then, the user can use something like that in order to force the use of a temporary table.
I think it can be useful.
I agree with you with the object oriented approach, but a fremework developer should handle all kinds of situations, including those that the developer can’t change the database schema.