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...
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.
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
