sql-server-2008 - 如何首先在EF代码中更改/设置排序规则

Question

我想先更改由ef代码创建的整个数据库的排序规则，我尝试通过在创建后运行脚本来做到这一点，但是它不起作用，

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

创建数据库之前是否可以设置排序规则？

那是我得到的异常(exception):

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

一种解决方法是执行sql命令创建数据库，而不是使用_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

");

我已经留出一些间距以使其更易于阅读，显然将databasename更改为所需的名称，还将PathToData和PathToLog更改为。

如果将服务器的排序规则更改为French_CI_AI，则将使用此排序规则创建所有新数据库，但这并非总是最佳选择，并且如果您已超出开发阶段，则会造成麻烦，因为它也会影响temp_db数据库(临时表和其他临时对象)。