1

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.

Ogglas
  • 62,132
  • 37
  • 328
  • 418
  • Why are you altering the database in the seed method instead of a migration? – S.C. May 17 '18 at 18:48
  • @S.C. I don't want to write any custom code in a migration, only have the auto generated. Someone should be able to delete all migrations, add a new migration and the database structure should look exactly the same. – Ogglas May 17 '18 at 18:52
  • That's a very limiting point of view. What about procedures, views, etc? If someone deletes all migrations and starts over your model will get out of sync with the `__MigrationHistory` table as well, so that goal only works for early development anyway. You're trying to both alter a database and add data to it. SQL does not allow both of those in the same transaction and EF tries to prevent that as well for good reasons. There are more maintainable built-in ways of accomplishing what you're trying to accomplish. – S.C. May 17 '18 at 19:55
  • Duplicate question here: https://stackoverflow.com/questions/12054930/set-database-collation-in-entity-framework-code-first-initializer – S.C. May 17 '18 at 19:56
  • Possible duplicate of [Set database collation in Entity Framework Code-First Initializer](https://stackoverflow.com/questions/12054930/set-database-collation-in-entity-framework-code-first-initializer) – S.C. May 17 '18 at 19:56
  • @S.C. We are not in production so we can still do this. After production is of course another question. How would you do it if not a migration or seed? – Ogglas May 17 '18 at 20:04
  • See the linked question for some options for doing that without custom migrations. None of them are as clean as a custom migration but depending on your priorities you might like one of them. – S.C. May 17 '18 at 20:09

1 Answers1

4

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();
    }
}
Gert Arnold
  • 105,341
  • 31
  • 202
  • 291