CODEWITHSUNDEEP
sql-serverentity-frameworkef-code-firstentity-framework-6
user5871859
user5871859

Reputation:

Change Database Collation after migration?

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

Upvotes: 0

Views: 1509

Answers (1)

Diana
Diana

Reputation: 2226

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.

Upvotes: 2

Related Questions