How to change/set collation in EF Code first
ef-code-first, entity-framework-4.1, sql-server-2008
Solution
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).
Problem
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, IDictionary`2 parameters) +261 System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 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