Inserting data based on existing data in the database

[Thomas Ardal]

I want to write a migration step inserting some new rows, referencing
existing rows through foreign keys. I do not know the ids of these
foreign keys and even if I did, I would not want to hardcode these. In
pure SQL I could do something like this:

insert into A values (newid(), (select top 1 Id from B where some =
'foo'))

I can execute this sql through the Execute.Sql-method on
FluentMigrator. But I would rather have the best of both worlds and be
able to select Id from B first and then use the FluentMigrator Insert-
API to insert the row in A.

I've read a couple of posts about the Execute.WithConnection-method
which should make this possible. The above example would then be
written like this:

var id = 0;
Execute.WithConnection((c, t) => {
var command = c.CreateCommand();
command.Transaction = t;
command.CommandText = "select top 1 Id from B where some = 'foo'";
using (var reader = command.ExecuteReader()) {
while (reader.Read())
id = int.Parse(reader[0].ToString());
}
});

Insert.IntoTable("A").Row(new {Id = Guid.NewGuid(), BRef = id});

The only problem is, that the above code sample doesn't work. The
migration step added with the WithConnection-method is executed AFTER
the migration step added with the IntoTable-method.

I've also tried adding the Insert.IntoTable(...) call inside the
WithConnection-body. This causes FluentMigrator to throw a
NullReferenceException when calling Insert.IntoTable.

Any ideas how to fix this problem?

[Mike Bridge]

I don't think it will work; the Bref value gets bound right away when you call .Row().

An ugly workaround is to do a second Execute.WithConnection, because the Action doesn't get invoked until it's needed:

Execute.WithConnection((c, t) =>

            {

                var command = c.CreateCommand();

                command.Transaction = t;

                command.CommandText = "INSERT INTO A(id, BRef) VALUES(@id, @name)"

                command.Parameters.Add(new SqlParameter("@id", Guid.NewGuid());

                command.Parameters.Add(new SqlParameter("@name", id);

                command.ExecuteNonQuery();

            });

I prefer not to use the "Insert" syntax anyway since it uses an anonymous class, which is kind of brittle because it won't fail until runtime.  I'd rather use my own ORM objects so that changes to the schema show up at compile time.

In any case, It would be nice if you could pass in a Func to wrap the variable in a closure, but I don't see a way to do that in the current library.

-Mike