Set database mapping in Entity Framework Code-First Initializer

Question

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

I 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"); } } 

It seems to start normally when SQL Server is already configured for the same Latin1_General_CI_AS default mapping.

But if I specify another sort, say SQL_Latin1_General_CP1_CI_AS, this is not an 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 up sorting?

Answer 1

Team Interceptor Solution

It is definitely possible, although it’s a little hack. You can modify the CREATE DATABASE command with a command interceptor. Il will intercept all the commands sent to the database, recognize the command to create the database based on the regular expression, and change the text of the command using sorting.

Before creating a database

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

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) { } } 

Notes

Since the database is created with the correct sorting from the very beginning, all columns will automatically inherit this sorting, and after that you will not have ALTER.

Remember that this will affect the subsequent database creation that occurs within the application domain. This way you can remove the interceptor after creating the database.

Answer 2

I was able to change the sort using custom migration (EF6). I have automatic migration enabled. First you need to delete your database.

• Create the migration code by typing Add-Migration [YourCustomMigration] in the package manager console. ( First steps of migration )
• The first step is to create your migration class with the current model creation code in the Over () override. Add the ALTER DATABASE code BEFORE the table creation codes so that they are created using the database you need. 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 this moment creates a new migration class. All migration codes are executed in order.

Answer 3

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

• It seems that EF creates the database using the default server mapping, so one thing you can do is change this.
• You cannot change the sorting of the database in the Seed() method, but you can change the sorting of individual columns for a table (NOTE: there is no such thing as sorting a table, it refers to a column in a table). You will need to change the sorting of the columns separately.
• If you use migrations, you can change the mapping table column in your Up() method.

Since you are using the Seed() method, I would suggest the following (if necessary, change) in the Seed() method:

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

Hope this helps.

Answer 4

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

Answer 5

This is simply not possible using current versions of EF (EF6). However, at least EF6 + can now work with an existing database. We modified our deployment script so that the database is already created by our deployment script (including the default setting), and let EF6 work with the existing database (using the correct default sort).

If you absolutely need to create a database inside your code and not use anything other than EF (for example, you cannot create a database using ADO.NET), you need to contact seliosalex for the answer. However, this is the only solution we came up with to see my comment, it is a lot of work to do it right.