Home > C#, Magiq > Magiq-to-sql: Knowing which table should be updated

Magiq-to-sql: Knowing which table should be updated

Today I will follow with the Magiq saga. You can take a look to the previous posts:

This post is about a problem I had with Magiq-to-sql. For an update, what magiq will do is:

  1. Generate a linq-to-sql query dynamically in order to retrieve each value that should be assigned to each property and the where clause.
  2. Create a command for that query.
  3. Parse the selected columns.
  4. Create the update statement setting each defined column to each selected column and using the same where as for the linq-to-sql query command.

For example, suppose you have this update:

people.Where( x => x.Name == "Ivo" ).Set( x => x.NickName, x.Name + "wiblo" ).Update();

The linq-to-sql query would be:

people.Where( x => x.Name == "Ivo" ).Select( x => new { NickName = x.Name + "wiblo" }  );

The select sql statement:

SELECT [t0].[Name] + @p1 FROM [People] [t0] WHERE [t0].Name = @p2

The column:

[t0].[Name] + @p1

The generated update sql statement:

UPDATE [People] SET [NickName] = [t0].[Name] + @p1 FROM [People] [t0] WHERE [t0].Name = @p2

If you take a look closer, you will realize that it’s using the actual table name instead of the alias used in the FROM clause. This is actually wrong. It work with that simple query but it becomes more complicated as the query does. For example, when you start updating properties of an association:

people.Where( x => x.Name == "Ivo" ).Set( x => x.User.NickName, x.Name + "wiblo" ).Update();

It will generate this UPDATE sql clause:

UPDATE <<TABLE>> SET [NickName] = [t0].Name + @1 FROM [People] AS [t0] LEFT OUTER JOIN [Users] AS [t1] ON [t1].[Id] = [t0].[UserId] WHERE [t0].[Name] = @p0

It could use the actual table name (UPDATE [Users] SET…) but it would crash if you have tables with references to itself. Anyway, using the actual table generates an update that doesn’t work properly (it updates more rows, I don’t know why. It makes sense, but I didn’t research about it).
So… the big question: How do it know which table to use? Looking the where clause, it’s easy for a human to realize it, but how tell the machine to think that way? Furthermore, there’re times when linq-to-sql uses subselects that introduces new tables in the game. Beautiful.

Well… I must say it was pretty easy. The only thing it had to do is to add a property of that table to the dynamically generated select as the first column to retrieve, so Magiq will know that the table used in that column is the right one. For the last example, it will generate this linq-to-sql query:

people.Where( x => x.Name == "Ivo" ).Select( x => new {
                                               __TypeReference__ = x.User.Id,
                                               NickName = x.Name + "wiblo"
                                            });

And this select sql statement:

SELECT [t1].Id AS [__TypeReference__], [t0].Name + @1 AS [NickName] FROM [People] AS [t0] LEFT OUTER JOIN [Users] AS [t1] ON [t1].[Id] = [t0].[UserId] WHERE [t0].[Name] = @p0

Parsing the first column ([t1].Id AS [__TypeReference__]), it generates the following update sql statement:

UPDATE [t1] SET [NickName] = [t0].Name + @1 FROM [People] AS [t0] LEFT OUTER JOIN [Users] AS [t1] ON [t1].[Id] = [t0].[UserId] WHERE [t0].[Name] = @p0

I’m pretty happy with this 🙂

Advertisements
Categories: C#, Magiq Tags: , ,
  1. No comments yet.
  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: