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

Magiq: Updating associations in batch challenge

Today I started working in a new Magiq feature, batch update of associations.
The idea is to be able to run this:

contacts.Where( x => x.FirstName == "Ivo" )
        .Set( x => x.City.HasPeopleNamedIvo, true ).Update();

I know the example doesn’t make any sense, but it’s fine for the explanation purpose.
It was easy to implement in Magiq-to-objects (it needed some expression manipulation, but harmless). The problem begun when I tried to update a property used in the where condition using magiq-to-sql. Basically, for this magiq sentence:

contacts.Where( x => x.FirstName == "Ivo" && !x.City.HasPeopleNamedIvo )
        .Set( x => x.FirstName, "IvoWiblo" );
        .Set( x => x.City.HasPeopleNamedIvo, true )
        .Update();

it’s generating these queries:

UPDATE [Contacts] SET [t0].FirstName = @p1
                FROM [Contacts] AS [t0]
                LEFT OUTER JOIN [Cities] AS [t1] ON [t0].CityId = [t1].Id
                WHERE [t0].FirstName == @p0 AND [t1].HasPeopleNamedIvo = @2
UPDATE [Cities] SET [t1].HasPeopleNamedIvo = @p1
                FROM [Contacts] AS [t0]
                LEFT OUTER JOIN [Cities] AS [t1] ON [t0].CityId = [t1].Id
                WHERE [t0].FirstName == @p0 AND [t1].HasPeopleNamedIvo = @2

EDIT: I changed a little bit the queries just to reflect that “changing the order of the sets” is not the solution.

As you can see, once the first SQL is executed, the second one updates nothing, because there’s no more people with that FirstName.

I have not idea how to solve it, do you?

HasPeopleNamedIvo
Advertisements
  1. December 16, 2009 at 4:07 pm

    How about sth like this?

    UPDATE [Contacts] SET [t0].FirstName = @p1, [t1].HasPeopleNamedIvo = @p2
    FROM [Contacts] AS [t0]
    LEFT OUTER JOIN [Cities] AS [t1] ON [t0].CityId = [t1].Id
    WHERE [t0].FirstName == @p0 AND [t1].HasPeopleNamedIvo = @2

    The name of the parameters are wrong, but you get the idea. 🙂

    • ivowiblo
      December 16, 2009 at 4:19 pm

      Fabian:
      It doesn’t work. SQL says “The multi-part identifier “[t0].FirstName ” could not be bound.”

  2. Ivan
    December 16, 2009 at 4:12 pm

    Hi there.

    I think that there is a level problem with this example.
    These are two different updates, and should not be generated by a single .Update() call.

    Regards
    Ivan

    • ivowiblo
      December 16, 2009 at 4:14 pm

      Ivan:
      Why not? the api is object oriented and that should work fine. The problem here is how to translate this to a usefull sql sentence.

      • Ivan
        December 16, 2009 at 7:29 pm

        Ok, you are right, should be a way to put it to work.

        But, then again, I’m not comfortable with 2×1 updates.

  3. Mr PofMan
    December 16, 2009 at 4:14 pm

    Do you mean that this don’t work?
    UPDATE [Cities] SET [t1].HasPeopleNamedIvo = @p1

    6 FROM [Contacts] AS [t0]

    7 LEFT OUTER JOIN [Cities] AS [t1] ON [t0].CityId = [t1].Id

    8 WHERE [t0].FirstName == @p0 AND [t1].HasPeopleNamedIvo = @2

    UPDATE [Contacts] SET [t0].FirstName = @p1

    2 FROM [Contacts] AS [t0]

    3 LEFT OUTER JOIN [Cities] AS [t1] ON [t0].CityId = [t1].Id

    4 WHERE [t0].FirstName == @p0 AND [t1].HasPeopleNamedIvo = @2

    • ivowiblo
      December 16, 2009 at 4:24 pm

      Both are well formed sql sentences, and they do what they are supposed to do. But when you execute the first one, the database rows changed and the second sentence doesn’t update any record, because there are not rows that matches the where now (because these rows were changed by the first sql)

      • Mr PofMan
        December 16, 2009 at 4:35 pm

        I think there is a problem with the query, you’re updating the same fields that you’re tring to filter.

        i think that is not totaly correct

      • ivowiblo
        December 16, 2009 at 4:40 pm

        That’s exactly what I want to do!
        Look at the magiq sentence, it make sense for objects, right? well, I want to translate it to a proper sql. I can make a temporal table with the contact ids before the first update, but it’s ugly.

  4. Jonathan
    December 16, 2009 at 5:07 pm

    Maybe Mr Pofman is right, although even if in SQL is not totally supported Ivo can still want to support this in Magiq.
    I have a possible solution, I don’t think it is the best one though. But I will share it with you guys and keep thinking on a better one, less complex.

    You need to query first and select all the matched IDs of every table. And than you update every table instead using the matched IDs in the WHERE clause.
    So in your example you will be doing the following queries in the following order:

    SELECT [t0].Id AS ContactId, [t1].Id AS CityId
    FROM [Contacts] AS [t0]
    LEFT OUTER JOIN [Cities] AS [t1] ON [t0].CityId = [t1].Id
    WHERE [t0].FirstName == @p0 AND [t1].HasPeopleNamedIvo = @2

    And keep the IDs

    and then:

    UPDATE [Contacts] SET [t0].FirstName = @p1
    FROM [Contacts] AS [t0]
    WHERE [t0].Id IN (<>)

    and

    UPDATE [Cities] SET [t1].HasPeopleNamedIvo = @p1
    FROM [Contacts] AS [t0]
    WHERE [t0].id IN (<>)

    This solution will require 1 extra SELECT query.

    • ivowiblo
      December 16, 2009 at 5:51 pm

      The problem with that approach is if we have 10000000 records to update, the sql sentence will be huge.

  5. Jorge A. Teyssandier
    December 16, 2009 at 5:50 pm

    I think the only way to do this in sql is to create a view, that join both tables, do the update to that view, and then delete it… Of course, not a nice thing, but there is no way to do update to multiple tables with one query.

    The other thing that can be done is just recognize the property change (in a Set) so that the second query is done with that updated value.

    • ivowiblo
      December 16, 2009 at 5:54 pm

      About changing the set in the second query, I think it can’t be done if the set have this form: .Set( x=>x.Name, x => x.Name + “bla” )

      • Jorge A. Teyssandier
        December 16, 2009 at 6:13 pm

        I think creating the view sounds as sth strange, even to me, but can be tried… Creating a view is not sth that will cost effort to sql engine.

      • ivowiblo
        December 16, 2009 at 6:14 pm

        could you post a little pseudo sql code of how it would be?

  6. Jonathan
    December 16, 2009 at 6:26 pm

    Ok, as I said I think I found another way, but you should do it in just 1 UPDATE statement.
    Try this:

    UPDATE Contacts AS t1, Cities AS t2 SET t1.FirstName == ‘newvalue’, t2.HasPeopleNamedIvo = newvalue WHERE t1.FirstName = ‘oldvalue’ AND t2.HasPeopleNamedIvo = oldvalue AND t1. CityId = t2.Id

    Worked for me in MySQL, I guess it should work in MSSQL as well.

  7. Rodolfo
    December 16, 2009 at 7:31 pm

    In this particular case perhaps you can trick the queries with something like this

    UPDATE [Contacts] SET [t0].FirstName = @p1
    FROM [Contacts] AS [t0]
    LEFT OUTER JOIN [Cities] AS [t1] ON [t0].CityId = [t1].Id
    WHERE [t0].FirstName == @p0 AND [t1].HasPeopleNamedIvo = @2

    SET @p0 = @p1;

    UPDATE [Cities] SET [t1].HasPeopleNamedIvo = @p1
    FROM [Contacts] AS [t0]
    LEFT OUTER JOIN [Cities] AS [t1] ON [t0].CityId = [t1].Id
    WHERE [t0].FirstName == @p0 AND [t1].HasPeopleNamedIvo = @2

    It’s not a great solution, but it’s seem that with more simple than transact sql solutions

    • ivowiblo
      December 16, 2009 at 8:42 pm

      It wont work for something like
      contacts.Where( x => x.FirstName == “Ivo” && !x.City.HasPeopleNamedIvo )
      .Set( x => x.FirstName, x.FirstName + “Wiblo” );
      .Set( x => x.City.HasPeopleNamedIvo, true )

  8. Emiliano Ritiro
    December 16, 2009 at 9:49 pm

    Ideas to be analyzed:
    A sort of dependency tree: an algorithm can find the way that the update queries don’t overlap. If this algorithm fails, then make a temporary table to perform the updates.

    A different DSL: dont allow multiple updates, except with a special sentence with makes a temporary table. The goodness* of this is that you dont hide the special process of the query.

    *It depends of your criteria.

    • ivowiblo
      December 16, 2009 at 10:55 pm

      Yeah, that was what I think I will be doing, I will post the conclusions soon. About the different dsl, I like the idea of having a different way of do all this, so it easy to know when it’s needed, but the current API seems too straight forward and I don’t know if I want to change it.

  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: