sql server - 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.