Set database collation in Entity Framework Code-Fi

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

I have had the same problem a while ago. Possible solutions:

1. It appears that EF creates the database using the server default collation so one thing you could do is change that.
2. You cannot change the database collation within the Seed() method but you can change the collation of individual columns for a table (NOTE: there is no such thing as table collation, it does relate to column in a table). You will have to change each column's collation separately.
3. If you are using migrations, you could alter the table column collations within your Up() method.

As you are using the Seed() method, I would suggest the following (modify as appropriate) within the Seed() method:

context.Database.ExecuteSqlCommand(
@"ALTER TABLE MyTable ALTER COLUMN MyColumn NVARCHAR(max) COLLATE MyCollation NOT NULL");

Hope that helps.

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.

Answer 3

EF 5 now supports creating missing tables in an existing database with Code First, so you can create an empty database and set the collation correct, before running an CF on it.

Answer 4

I would like to explain why you should not use the seed method for this. If you change your database collation after any columns have been added there is a large risk for collation conflicts like below

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_100_CI_AS" in the equal to operation.

This is due to the fact that if you alter your database with ALTER DATABASE [YourDb] COLLATE [YourCollation] you will only change the databases collation and not previously created columns.

Example in T-SQL:

DECLARE @DBName nvarchar(50), @SQLString nvarchar(200)
SET @DBName = db_name();
SET @SQLString = 'ALTER DATABASE [' + @DBName + '] COLLATE Latin1_General_100_CI_AS'
EXEC(@SQLString)

/* Find Collation of SQL Server Database */
SELECT DATABASEPROPERTYEX(@DBName, 'Collation')
/* Find Collation of SQL Server Database Table Column */

SELECT name, collation_name
FROM sys.columns
WHERE OBJECT_ID IN (SELECT OBJECT_ID
FROM sys.objects
WHERE type = 'U'
AND name = 'AspNetUsers')
AND name = 'FirstName'

Due to this you need to change database collation before any columns are added or change every column separately. Possible solutions:

1. @MathieuRenda https://stackoverflow.com/a/42576705/3850405

I would put the DbInterception.Add in a class deriving from DbConfiguration or in Application_Start in Global.asax as recommended in the documentation. Note: Wherever you put this code, be careful not to execute DbInterception.Add for the same interceptor more than once, or you'll get additional interceptor instances.

public class ApplicationDbConfiguration: DbConfiguration
{
    public ApplicationDbConfiguration()
    {
        DbInterception.Add(new CreateDatabaseCollationInterceptor("Latin1_General_100_CI_AS"));
    }
}

I would also not inherit from the interface but instead use the implementation of DbCommandInterceptor as Microsoft does in their examples.

using System.Data.Common;
using System.Data.Entity.Infrastructure.Interception;
using System.Text.RegularExpressions;

namespace Application.Repositories.EntityFramework
{
    public class CreateDatabaseCollationInterceptor : DbCommandInterceptor
    {
        private readonly string _collation;

        public CreateDatabaseCollationInterceptor(string collation)
        {
            _collation = collation;
        }

        public override void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
        {
            // Works for SQL Server
            if (Regex.IsMatch(command.CommandText, @"^create database \[.*]$"))
            {
                command.CommandText += " COLLATE " + _collation;
            }
        }
    }
}

More information here: https://docs.microsoft.com/en-us/aspnet/mvc/overview/getting-started/getting-started-with-ef-using-mvc/connection-resiliency-and-command-interception-with-the-entity-framework-in-an-asp-net-mvc-application

2. @steliosalex: https://stackoverflow.com/a/22895703/3850405. Note that changing every column might not be enough either. You also need to handle metadata and parameters for stored procedure and similar get the collation that the database had when these where created. Changing collation completely requires a create database command with the right collation.

3. @RahmiAksu https://stackoverflow.com/a/31119371/3850405 NOTE: This is not a good solution in my opinion but if you use it edit the very first migration. Can't be used if the database is already in production. If you have a seed method the exception Resetting the connection results in a different state than the initial login will be thrown.

Your Seed SqlException can be solved by using a plain ADO.Net connection, so the context's connection won't be reset. However as mentioned above this will probably cause a lot of errors later.

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();
    }
}

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.

Source:

https://stackoverflow.com/a/50400609/3850405

Answer 5

It's simply not possible using current versions of EF (EF6). However, at least EF6+ can now work with already existent database. We've changed our deployment scenario such that the database is already created by our deployment script (incl. the default collation) and let EF6 work with the existing database (using the correct default collation).

If you absolutely have to create the database inside your code and cannot use anything else than EF (e.g. you are not able to create the database using ADO.NET) then you have to go for seliosalex answer. It's the only solution we came up, however, see my comment, it is a lot of work to do it right.

Answer 6

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.