Home > C#, Magiq > Magiq: Updating associations in batch challenge result

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!

  1. Emiliano Ritiro
    December 17, 2009 at 4:14 pm

    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.

    • ivowiblo
      December 17, 2009 at 4:35 pm

      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.

  2. Emiliano Ritiro
    December 17, 2009 at 6:38 pm

    Mmm… No, I’m saying something like:

    contacts.Where( x => x.FirstName == “Ivo”)
    .Set( x => x.FirstName, “IvoWiblo”)
    .Set( x => x.City.HasPeopleNamedIvo, true)

    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.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: