How to change/set collation in EF Code first

Question

I want to change Collation of the whole database created by ef code first, I try to do it by running a script after creation but it does not works,

_dbContext.Database.Delete();
_dbContext.Database.CreateIfNotExists();
_dbContext.Database.ExecuteSqlCommand("ALTER DATABASE afi COLLATE French_CI_AI");

Is it possible to set the collation before creating the database?

That is the exception I get :

Resetting the connection results in a different state than the initial login. The login fails. Login failed for user 'afi'. A severe error occurred on the current command. The results, if any, should be discarded.

[SqlException (0x80131904): Resetting the connection results in a different state than the initial login. The login fails.

Login failed for user 'afi'. A severe error occurred on the current command. The results, if any, should be discarded.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +388
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +688
System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +4403
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +84
System.Data.SqlClient.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction transaction, TdsParserStateObject stateObj, Boolean isDelegateControlRequest) +1370
System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon(TransactionRequest transactionRequest, String transactionName, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest) +674
System.Data.SqlClient.SqlInternalConnection.BeginSqlTransaction(IsolationLevel iso, String transactionName) +547
System.Data.SqlClient.SqlInternalConnection.BeginTransaction(IsolationLevel iso) +18
System.Data.SqlClient.SqlConnection.BeginDbTransaction(IsolationLevel isolationLevel) +211
System.Data.EntityClient.EntityConnection.BeginDbTransaction(IsolationLevel isolationLevel) +155

[EntityException: An error occurred while starting a transaction on the provider connection. See the inner exception for details.]
System.Data.EntityClient.EntityConnection.BeginDbTransaction(IsolationLevel isolationLevel) +4298876
System.Data.EntityClient.EntityConnection.BeginTransaction() +10
System.Data.Objects.ObjectContext.SaveChanges(SaveOptions options) +538 System.Data.Entity.Internal.InternalContext.SaveChanges() +218 Afi.Domain.Storage.AfiDbContext.SaveChanges() in c:\inetpub\wwwroot\afi\src\Domain\Storage\AfiDbContext.cs:190
Afi.Domain.Storage.EntitySession.Commit() in c:\inetpub\wwwroot\afi\src\Domain\Storage\EntitySession.cs:54
Afi.Web.Controllers.CIController.Seed(Boolean excludeSomeData) in c:\inetpub\wwwroot\afi\src\Web\Controllers\CIController.cs:263
Afi.Web.Controllers.CIController.Index() in c:\inetpub\wwwroot\afi\src\Web\Controllers\CIController.cs:89
lambda_method(Closure , ControllerBase , Object[] ) +81
System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary2 parameters) +261 System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary2 parameters) +39
System.Web.Mvc.Async.<>c_DisplayClass42.b_41() +34 System.Web.Mvc.Async.<>c_DisplayClass39.b_33() +124 System.Web.Mvc.Async.<>c_DisplayClass4f.b_49() +837307 System.Web.Mvc.Async.<>c_DisplayClass4f.b_49() +837307 System.Web.Mvc.Async.<>c_DisplayClass37.b_36(IAsyncResult asyncResult) +15
System.Web.Mvc.Async.<>c_DisplayClass2a.b_20() +33 System.Web.Mvc.Async.<>c_DisplayClass25.b_22(IAsyncResult asyncResult) +837892
System.Web.Mvc.<>c_DisplayClass1d.b_18(IAsyncResult asyncResult) +28
System.Web.Mvc.Async.<>c_DisplayClass4.b_3(IAsyncResult ar) +15 System.Web.Mvc.Controller.EndExecuteCore(IAsyncResult asyncResult) +65
System.Web.Mvc.Async.<>c_DisplayClass4.b_3(IAsyncResult ar) +15 System.Web.Mvc.Controller.EndExecute(IAsyncResult asyncResult) +51
System.Web.Mvc.<>c_DisplayClass8.b_3(IAsyncResult asyncResult) +42
System.Web.Mvc.Async.<>c_DisplayClass4.b_3(IAsyncResult ar) +15 System.Web.Mvc.MvcHandler.EndProcessRequest(IAsyncResult asyncResult) +51
System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +606 System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +288

Are you aware that even if you change collation on the database, any objects that were created before the change will persist the collation? If you have a table with 100 rows in it. They will hold the old collation whereas the new entries will have the new collation. — twoleggedhorse, Dec 13 '12 at 13:23
Also, when you create a database and you do not specify collation, it takes the collation of the server. Is the server set to French_CI_AI? — twoleggedhorse, Dec 13 '12 at 13:25
we want to change the collation between the time we create de database and the time EF Code First create the tables but we don't know how so the table should be empty — VinnyG, Dec 13 '12 at 15:07
What is the server's collation? In SSMS, right click the server and select Properties. Under General you will see the entry "Server Collation"... — twoleggedhorse, Dec 14 '12 at 09:58
Possible duplicate of [Set database collation in Entity Framework Code-First Initializer](http://stackoverflow.com/questions/12054930/set-database-collation-in-entity-framework-code-first-initializer) — Mathieu Renda, Mar 10 '17 at 23:05

score 6 · Accepted Answer · answered Dec 14 '12 at 10:24

6

A workaround may be to execute a sql command to create the database, rather than use _dbContext.Database.CreateIfNotExists();

_dbContext.Database.ExecuteSqlCommand("

CREATE DATABASE [databasename] ON PRIMARY ( NAME = N'databasename', FILENAME = N'c:\PathToData\databasename.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB ) LOG ON ( NAME = N'databasename_log', FILENAME = N'c:\PathToLog\databasename_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%) COLLATE French_CI_AI

");

I've put in a little spacing to make it easier to read, obviously change databasename to the name you want, and also PathToData and PathToLog.

If you change the collation of the server to French_CI_AI, all new databases will be created using this collation but it's not always the best choice and will cause headaches if you are past the developement stage as it affects the temp_db database as well (temporary tables and other temporary objects).

answered Dec 14 '12 at 10:24

twoleggedhorse

4,938
4
23
38

You can generate the script by the following steps in ssms. Right click on the databases folder -> new database. Setup how you wish and then click on the script icon at the top (not the okay button). Choose the "Script action to new query window" option to generate your sql. You can pretty much ignore most of the settings after the main create dabase command. – twoleggedhorse Dec 14 '12 at 10:28
If you're worried about the uglies in this workaround you could easily enough expand the code to follow the existing conventions to determine database name and db file location. Otherwise, you do risk screwing up other developers trying to get a local build going or trying to do something slightly unusual in their build (like maintain a few different DBs for testing on a single project). – Chris Moschini Dec 15 '12 at 20:00
It's a good solution but I'm not quite sure if it's the solution to my problem since it's not done using EF. – VinnyG Dec 18 '12 at 20:08
Do you have an .edmx file in your solution? – twoleggedhorse Dec 19 '12 at 06:41

score 2 · Answer 2 · answered Jan 29 '15 at 22:59

2

For c# SqlConnection.ClearAllPools(); before the next connection is initiated works as well.

answered Jan 29 '15 at 22:59

user2916800

21
2

thanks, that line saved me a lot while trying to seed the database with `ExecuteSqlCommand()` in entity framework 6 `System.Data.SqlClient.SqlConnection.ClearAllPools();` – Ahmed Mohammed Jun 19 '19 at 11:28

score 0 · Answer 3 · edited Sep 09 '13 at 22:27

0

only add [ ] to name database,

_dbContext.Database.ExecuteSqlCommand("ALTER DATABASE [afi] COLLATE French_CI_AI");

edited Sep 09 '13 at 22:27

gustavohenke

40,997
14
121
129

answered Sep 09 '13 at 22:08

Femavelsa

1

score 0 · Answer 4 · edited May 23 '17 at 11:46

If you want to change collation of the current database, you can use this snippet

_dbContext.Database.ExecuteSqlCommand(TransactionalBehavior.DoNotEnsureTransaction,
                        "ALTER DATABASE CURRENT COLLATE Cyrillic_General_CI_AS");

or

_dbContext.Database.ExecuteSqlCommand(TransactionalBehavior.DoNotEnsureTransaction,
                            string.Format("ALTER DATABASE [{0}] COLLATE Cyrillic_General_CI_AS", _dbContext.Database.Connection.Database));

Alter Database in Entity Framework 6

How to change/set collation in EF Code first

4 Answers4

Linked