Set database collation in Entity Framework Code-First Initializer

Question

I want to set the default collation for a database, when Entity Framework Code First creates it.

I've tried the following:

public class TestInitializer<T> : DropCreateDatabaseAlways<T> where T: DbContext {     protected override void Seed(T context)     {         context.Database.ExecuteSqlCommand("ALTER DATABASE [Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE");         context.Database.ExecuteSqlCommand("ALTER DATABASE [Test] COLLATE Latin1_General_CI_AS");         context.Database.ExecuteSqlCommand("ALTER DATABASE [Test] SET MULTI_USER");     } } 

This appears to run OK when SQL Server is already set to the same default collation Latin1_General_CI_AS.

But if I specify a different collation, say SQL_Latin1_General_CP1_CI_AS this fails with the error,

System.Data.SqlClient.SqlException: Resetting the connection results in a different  state than the initial login. The login fails. 

Can anyone advise how I can set the collation please?

Answer 1

Solution with a command interceptor

It is definitely possible, though it's a bit of a hack. You can alter the CREATE DATABASE command with a command interceptor. Il will intercept all the commands sent to the database, recognize the database creation command based on a regex expression, and alter the command text with your collation.

Before database creation

DbInterception.Add(new CreateDatabaseCollationInterceptor("SQL_Romanian_Cp1250_CI_AS_KI_WI")); 

The interceptor

public class CreateDatabaseCollationInterceptor : IDbCommandInterceptor {     private readonly string _collation;      public CreateDatabaseCollationInterceptor(string collation)     {         _collation = collation;     }      public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext) { }     public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)     {         // Works for SQL Server         if (Regex.IsMatch(command.CommandText, @"^create database \[.*]$"))         {             command.CommandText += " COLLATE " + _collation;         }     }     public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext) { }     public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext) { }     public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext) { }     public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext) { } } 

Remarks

Since the database is created with the right collation from the start, all the columns will automatically inherit that collation and you wan't have to ALTER them afterwards.

Be aware that it will impact any later database creation occurring inside the application domain. So you might want to remove the interceptor after the database is created.

Answer 2

I was able to change collation with a custom migration (EF6). I have automatic migrations enabled. You need to delete your DB first.

1. Create the migration code by typing Add-Migration [YourCustomMigration] in Package Manager Console. (Code First Migrations)
2. First step should create your migration class with current model creation code in the Up() override. Add your ALTER DATABASE code BEFORE the table creation codes so they are created using the database collation you want. Also, note the suppressTransaction flag:

public override void Up() { Sql("ALTER DATABASE [YourDB] COLLATE [YourCollation]", suppressTransaction: true); [...Your DB Objects Creation codes here...] }

Each update-database command issued from then on creates a new migration class. All migration codes are executed in order.