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

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

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

Update-Database tries to do an automatic migration even with automatic migrations disabled

public class Configuration : DbMigrationsConfiguration<bailencasino.com.dal.Context.BlncnoContext> { public Configuration() { AutomaticMigrationsEnabled = false; AutomaticMigrationDataLossAllowed = true; } ...Add ...AutomaticMigrationDataLossAllowed = true;... so you assume that you want to allow EF to add remove SQL obj...
ef-migrations entity-framework
asked by user761172

EF Data migrations won't detect changes when adding new migration

I had a similar problem where a new migration was not being found, and so ...update-database... was giving me the following error no matter what I did:...Unable to update database to match the current model because there are pending changes and automatic migration is disabled. Either write the pending model changes to a code-based migration or enab...
ef-migrations entity-framework
asked by Shaun Wilson

EF6 Beta1 - db.Database.CreateIfNotExists(); doesn't create the database anymore after enabling the migration

EF team responded me that it's a new changes in EF. Please refer to this thread. ...https://entityframework.codeplex.com/discussions/450998
entity-framework entity-framework-6
asked by Michael Sync

Update-Database fails due to Pending Changes, but Add-Migration Creates a Duplicate Migration

I see this all the time also. I don't know why, wish i did, but my solution is to do a add-migration which will make a duplicate. Now this duplicate opens in the editor and then i edit it, so that the Up and Down methods are empty. So the result is a migration file that does nothing! VS is happy and you can do the update-database without errors (un...
c# ef-migrations entity-framework
asked by Lars

Change Database during runtime in Entity Framework, without changing the Connection

You can take a look at: ...SO question... about passing existing SQL Connection to EntityFramework Context ...and at ...this article... describing how to change database on existing connection....Please let me know if any additional help is needed....Edited... Updated 2nd link to point to ...SqlConnection.ChangeDatabase... method.... So eventual...
asp.net-web-api c# entity-framework sql-server
asked by alex.b

EF Add-Migration indicates "No pending explicit migrations" but Update-Database complains "..there are pending changes"

For me the issue was that we had renamed the namespace of the migration 2014123456_Initial.cs.... But VS had not regenerated the namespace in its associated 2014123456_Initial.Designer.cs....Once the Designer.cs was changed to use the same namespace, it all started working again.
ef-migrations entity-framework
asked by demoncodemonkey

Code-first, setting database collation

I solved the issue by creating the DB myself. The base class is then creating the tables into the empty DB:... public class MyInitializer : CreateDatabaseIfNotExists<MasterDataModel> { public override void InitializeDatabase(MasterDataModel context) { if(!context.Database.Exists()) { us...
entity-framework entity-framework-6
asked by huer12

Entity Framework 6 Migrations: How to disable automatic Update-Database on Database initialize?

Migrations also have a Seed method you can override that takes place after the Up() method. You can use the AddOrUpdate command in case data may exist. ...http://www.asp.net/web-api/overview/data/using-web-api-with-entity-framework/part-3
ef-migrations entity-framework-6
asked by Steve Greene

EF migration with new table collation error

The collation of both columns 'dbo.AspNetUsers.Id' and 'Feeds.UserId' should be the same, To make them the same you can modify the collation of one of those columns using below sample code:...context.Database.SqlCommand("ALTER TABLE MyTable ALTER COLUMN MyColumn VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS NULL");...Hope this helps....Regards,....
azure-sql-database database-migration ef-migrations entity-framework entity-framework-6
asked by Alberto Morillo

How do I change connection string for Update-Database ef migrations?

All I had to do was the following in PowerShell: ...$env:ASPNETCORE_ENVIRONMENT='LocalWindows' dotnet ef database update...or ...$env:ASPNETCORE_ENVIRONMENT='LocalWindows' Update-Database...Turning on verbose with ...Update-Database -verbose... also shows you the environment in the output to make sure it's hitting the correct one....What doesn't wo...
asp.net-core-2.0 dotnet-cli ef-migrations entity-framework-6
asked by Cymricus

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

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