Change Database Collation after migration?

ef-code-first entity-framework entity-framework-6 sql-server

Question

I have an MVC5 project and I populated my tables to the database via Entity Framework 6 - Code First migration. When looking to the tables, I ses that some characters do not displayed correctly and the Database Collation is SQL_Latin1_General_CP1_CI_AS instead of French_CI_AS.

1) What should be made in order to set the Database Collation while creating database via Code First? I found the following method below, but not sure if it is the best option for this purpose?

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

On the other hand, when using this script, I encounter "ALTER DATABASE failed. The default collation of database 'DbName' cannot be set to French_CI_AS" error.

2) Is it possible to change the Database Collation (via Code First or SQL) after adding some data to the related tables?

Any help would be appreciated...

1
1
11/19/2016 11:36:07 PM

Accepted Answer

It should be possible to change the collation even after adding data. I guess your problem comes from the fact that you need to put the database in single-user mode while you are executing the collation change. The database must be locked to prevent other connections from using it. After you finish you restore the multi-user mode.

If this is your case you should be getting this error in addition to the one you show in your question:

The database could not be exclusively locked to perform the operation.

The migration code to fix it:

public override void Up()
{
    Sql("ALTER DATABASE [YourDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;");

    Sql("ALTER DATABASE [YourDB] COLLATE [YourCollation];");

    Sql("ALTER DATABASE [YourDB] SET MULTI_USER;");

    [...Your DB Objects Creation codes here...]
}

I think you should remove the supressTransaction parameter. You should probably run this operation in a single transaction, in case some step fails.

2
11/19/2016 11:20:17 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow