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

Answer 1

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