Entity Framework Seed Deployment SQL Script error

Question

As part of my Entity Framework deployment I'm trying to deploy some scripts onto a newly created database, however I got the following error;

ALTER DATABASE statement not allowed within multi-statement transaction.

My Code;

internal sealed class Configuration : DbMigrationsConfiguration<DBContext>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = true;
    }

    protected override void Seed(DBContext context)
    {
        context.Database.ExecuteSqlCommand(
            @"ALTER DATABASE MyDB SET ENABLE_BROKER
            CREATE QUEUE NewCarShareQueue;
            CREATE SERVICE NewCarShareService ON QUEUE NewNewCarShareQueue 
               ([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);");
    }
}

I've tried the following as well, making sure only one transaction is going through at once, and not shown here but disposing the current context and recreating it;

internal sealed class Configuration : DbMigrationsConfiguration<DBContext>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = true;
    }

    protected override void Seed(DBContext context)
    {
        context.SaveChanges();
        context.Database.ExecuteSqlCommand(
           @"ALTER DATABASE MyDB SET ENABLE_BROKER");
    }
}

Anders Abel · Accepted Answer · 2013-11-15 19:12:29Z

2

The Seed command is run after every successful migration run. It actually runs even if no migrations were pending. So it is a bad place to do any altering of the DB structure.

It is better to create a migration and do the ALTERs in there. When your model and migrations are in sync, run add-migration EnableQueue in the PM console. That will give you an empty migration that you can now add your own statements to.

Anyways, running SQL statements in a separate transaction in the Seed method is an interesting problem. Something like this should work (haven't compiled it myself, so there's probably a few typos in it):

protected override void Seed(DBContext context)
{
  using(var tx = new TransactionScope(TransactionScopeOption.RequiresNew))
  using(var conn = new SqlConnection(context.Database.Connection.ConnectionString))
  {
    conn.Open()
    var command = conn.CreateCommand();
    command.CommandText = "ALTER DATABASE WHATEVER YOU WANT TO DO";
    command.ExecuteNonQuery();

    tx.Complete();
  }
}

answered Nov 15, 2013 at 19:12

Anders Abel

69.6k18 gold badges154 silver badges221 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

qbik Over a year ago

This works if you remove the TransactionScope. Otherwise, I got the same error: "ALTER DATABASE statement not allowed within multi-statement transaction"

Collectives™ on Stack Overflow

Entity Framework Seed Deployment SQL Script error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related