c# - Entity Framework seed -> SqlException: Resetting the connection results in a different state than the initial login. The login fails.

Question

I get the following exception when running the seed method for Entity Framework. I only get the exception once, if I run the seed method a second time when the database has already been altered the code works. What can I do so that I don't have to run the code twice when creating the database the first time? I wan't to use seed and not alter the database using a custom migration.

SqlException: Resetting the connection results in a different state than the initial login. The login fails. Login failed for user ''. Cannot continue the execution because the session is in the kill state.

protected override void Seed(Repositories.EntityFramework.ApplicationDbContext context)
{
    context.Database.ExecuteSqlCommand(TransactionalBehavior.DoNotEnsureTransaction, 
        string.Format("ALTER DATABASE [{0}] COLLATE Latin1_General_100_CI_AS", context.Database.Connection.Database));

    //Exception here
    context.Roles.AddOrUpdate(
           role => role.Name,
           new ApplicationRole() { Name = RoleConstants.SystemAdministrator }
    );
}

If I don't use TransactionalBehavior.DoNotEnsureTransaction I get the exception on context.Database.ExecuteSqlCommand

ALTER DATABASE statement not allowed within multi-statement transaction.

Answer 1

You can fix this issue by using a plain ADO.Net connection, so the context's connection won't be reset:

using (var conn = new SqlConnection(context.Database.Connection.ConnectionString))
{
    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandText = 
            string.Format("ALTER DATABASE [{0}] COLLATE Latin1_General_100_CI_AS",
                context.Database.Connection.Database));
        conn.Open();
        cmd.ExecuteNonQuery();
    }
}