1

Using System.Data.Entity.Migrations.DbMigrator

I have a code-first migration, where I purposefully added an error at the end of the Up() method. 

Sql("SELECT * FROM DOES_NOT_EXIST"); // Simulate error in migration

Now calling Migrate() throws SqlException as expected, but the interesting part is when I investigate with Sql Profiler.

In the profiler I can see queries, which are part of the migration (dropping or adding columns, modifying data, etc.) The last query is in fact SELECT * FROM DOES_NOT_EXIST.

Why all these previous queries from a migration seem to have no effect?

Almost like some kind of transaction was rolled back. But the migration isn't running within a transaction...

Full Up() code:

        public override void Up()
    {
        AddColumn("dbo.DummyEntities", "DifferentValue", c => c.String()); 
        DropColumn("dbo.DummyEntities", "Value");

        Sql(@"INSERT INTO [dbo].[DummyEntities]([DifferentValue]) VALUES ('MigratingToInvalid1')");
        Sql(@"INSERT INTO [dbo].[DummyEntities]([DifferentValue]) VALUES ('MigratingToInvalid2')");

        // Simulate invalid migration command, this will throw an exception
        Sql("SELECT * FROM DOES_NOT_EXIST");
    }
Eiver
  • 2,594
  • 2
  • 22
  • 36
  • 1
    IIRC, DDL statements can't run in a transaction (or it depends on the database engine) **Edit**: partially correct. see [here](https://msdn.microsoft.com/en-us/library/ms189122.aspx). some transaction levels aren't supported, some are. –  Feb 23 '17 at 14:47
  • _"Why all these previous queries from a migration seem to have no effect?"_ - which queries exactly? What did you expect to see, what did you actually see? – CodeCaster Feb 23 '17 at 14:48
  • I am completely sure for 2 reasons. 1 I do not see a transaction start/commit in the profiler unless I am blind. 2. If I run 2 migrations in parallel in 2 threads I actually get an error when both of them attempt to do the same actions in parallel and that should not happend if there was a transaction. – Eiver Feb 23 '17 at 14:50
  • @CodeCaster I added full code for the Up() method. I expect new column to be created. Old to be dropped. Existing data to be gone. New data to be inserted. I see all these queries in the profiler, but after the migration fails the DB is still in the old format with old data unharmed, which is good but how does it work? – Eiver Feb 23 '17 at 14:56
  • So it is actually executed within transaction. I just had the same issue as in http://stackoverflow.com/questions/6635445/transactionscope-where-is-begin-transaction-on-sql-profiler Now I feel really dumb. – Eiver Feb 23 '17 at 15:36

0 Answers0