在实体框架代码优先初始化器中设置数据库排序

I want to set the default collation for a database, when Entity Framework Code First creates it.

我想为数据库设置默认的排序，当实体框架代码首先创建它时。

I've tried the following:

我试过以下:

public class TestInitializer<T> : DropCreateDatabaseAlways<T> where T: DbContext
{
    protected override void Seed(T context)
    {
        context.Database.ExecuteSqlCommand("ALTER DATABASE [Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE");
        context.Database.ExecuteSqlCommand("ALTER DATABASE [Test] COLLATE Latin1_General_CI_AS");
        context.Database.ExecuteSqlCommand("ALTER DATABASE [Test] SET MULTI_USER");
    }
}

This appears to run OK when SQL Server is already set to the same default collation Latin1_General_CI_AS.

当SQL Server已经被设置为相同的默认collation Latin1_General_CI_AS时，这似乎可以运行。

But if I specify a different collation, say SQL_Latin1_General_CP1_CI_AS this fails with the error,

但是如果我指定了一个不同的排序规则，比如SQL_Latin1_General_CP1_CI_AS，

System.Data.SqlClient.SqlException: Resetting the connection results in a different 
state than the initial login. The login fails.

Can anyone advise how I can set the collation please?

谁能告诉我怎么设置排序规则吗?

6 个解决方案

#1

Solution with a command interceptor

It is definitely possible, though it's a bit of a hack. You can alter the CREATE DATABASE command with a command interceptor. Il will intercept all the commands sent to the database, recognize the database creation command based on a regex expression, and alter the command text with your collation.

这绝对是可能的，尽管这有点像黑客。可以使用命令拦截器修改CREATE DATABASE命令。Il将拦截发送到数据库的所有命令，根据regex表达式识别数据库创建命令，并使用排序规则修改命令文本。

Before database creation

DbInterception.Add(new CreateDatabaseCollationInterceptor("SQL_Romanian_Cp1250_CI_AS_KI_WI"));

The interceptor

public class CreateDatabaseCollationInterceptor : IDbCommandInterceptor
{
    private readonly string _collation;

    public CreateDatabaseCollationInterceptor(string collation)
    {
        _collation = collation;
    }

    public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext) { }
    public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
        // Works for SQL Server
        if (Regex.IsMatch(command.CommandText, @"^create database \[.*]$"))
        {
            command.CommandText += " COLLATE " + _collation;
        }
    }
    public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext) { }
    public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext) { }
    public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext) { }
    public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext) { }
}

Remarks

Since the database is created with the right collation from the start, all the columns will automatically inherit that collation and you wan't have to ALTER them afterwards.

由于从一开始就使用正确的排序规则创建数据库，所以所有列都将自动继承该排序规则，之后您不必修改它们。

Be aware that it will impact any later database creation occurring inside the application domain. So you might want to remove the interceptor after the database is created.

请注意，它将影响在应用程序域中进行的任何后续数据库创建。因此，您可能希望在创建数据库之后删除拦截器。

#2

I was able to change collation with a custom migration (EF6). I have automatic migrations enabled. You need to delete your DB first.

我可以通过自定义迁移(EF6)更改排序规则。我启用了自动迁移。你需要先删除你的数据库。

1. Create the migration code by typing Add-Migration [YourCustomMigration] in Package Manager Console. (Code First Migrations)
2. 通过在包管理器控制台输入Add-Migration [YourCustomMigration]来创建迁移代码。(代码首先迁移)
3. First step should create your migration class with current model creation code in the Up() override. Add your ALTER DATABASE code BEFORE the table creation codes so they are created using the database collation you want. Also, note the suppressTransaction flag:
4. 第一步应该使用Up()重写中的当前模型创建代码创建迁移类。在表创建代码之前添加ALTER数据库代码，以便使用所需的数据库排序规则创建它们。另外，注意stransaction标志:

public override void Up() { Sql("ALTER DATABASE [YourDB] COLLATE [YourCollation]", suppressTransaction: true); [...Your DB Objects Creation codes here...] }

public override void () {Sql(“ALTER DATABASE [YourDB] COLLATE [YourCollation])”，suppressTransaction: true);[…您的DB对象在这里创建代码……]}

Each update-database command issued from then on creates a new migration class. All migration codes are executed in order.

此后发出的每个update-database命令都创建一个新的迁移类。所有迁移代码都按顺序执行。

#3

I have had the same problem a while ago. Possible solutions:

我刚才也遇到过同样的问题。可能的解决方式:

1. It appears that EF creates the database using the server default collation so one thing you could do is change that.
2. 似乎EF使用服务器默认排序规则创建数据库，所以您可以做的一件事就是更改它。
3. You cannot change the database collation within the Seed() method but you can change the collation of individual columns for a table (NOTE: there is no such thing as table collation, it does relate to column in a table). You will have to change each column's collation separately.
4. 不能更改Seed()方法中的数据库排序规则，但可以更改表中各个列的排序规则(注意:没有表排序规则，它确实与表中的列相关)。您必须分别更改每一列的排序规则。
5. If you are using migrations, you could alter the table column collations within your Up() method.
6. 如果使用迁移，可以在Up()方法中更改表列排序。

As you are using the Seed() method, I would suggest the following (modify as appropriate) within the Seed() method:

由于您正在使用Seed()方法，我建议在Seed()方法中采用以下方法(酌情修改):

context.Database.ExecuteSqlCommand(
@"ALTER TABLE MyTable ALTER COLUMN MyColumn NVARCHAR(max) COLLATE MyCollation NOT NULL");

Hope that helps.

希望有帮助。

#4

It's simply not possible using current versions of EF (EF6). However, at least EF6+ can now work with already existent database. We've changed our deployment scenario such that the database is already created by our deployment script (incl. the default collation) and let EF6 work with the existing database (using the correct default collation).

使用当前版本的EF (EF6)是不可能的。但是，至少EF6+现在可以使用已经存在的数据库。我们已经更改了部署场景，使数据库已经由部署脚本(包括默认排序)创建，并让EF6与现有数据库(使用正确的默认排序)一起工作。

If you absolutely have to create the database inside your code and cannot use anything else than EF (e.g. you are not able to create the database using ADO.NET) then you have to go for seliosalex answer. It's the only solution we came up, however, see my comment, it is a lot of work to do it right.

如果您必须在代码中创建数据库，并且不能使用EF之外的任何东西(例如，您不能使用ADO.NET创建数据库)，那么您必须使用seliosalex。这是我们提出的唯一解决方案，但是，请见我的评论，做正确的事情需要大量的工作。

#5

EF 5 now supports creating missing tables in an existing database with Code First, so you can create an empty database and set the collation correct, before running an CF on it.

EF 5现在支持先用代码在现有数据库中创建缺失的表，因此您可以创建一个空数据库并在其上运行CF之前正确设置排序。

#6

I would like to explain why you should not use the seed method for this. If you change your database collation after any columns have been added there is a large risk for collation conflicts like below

我想解释为什么你不应该使用种子方法。如果在添加了任何列之后更改数据库排序，那么就会有如下的排序冲突的大风险。

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_100_CI_AS" in the equal to operation.

无法解决“SQL_Latin1_General_CP1_CI_AS”和“Latin1_General_100_CI_AS”之间的排序冲突。

This is due to the fact that if you alter your database with ALTER DATABASE [YourDb] COLLATE [YourCollation] you will only change the databases collation and not previously created columns.

这是因为如果您使用alter database [YourDb] COLLATE [YourCollation]来修改数据库，那么您将只更改数据库排序，而不更改以前创建的列。

Example in T-SQL:

在t - sql的例子:

DECLARE @DBName nvarchar(50), @SQLString nvarchar(200)
SET @DBName = db_name();
SET @SQLString = 'ALTER DATABASE [' + @DBName + '] COLLATE Latin1_General_100_CI_AS'
EXEC(@SQLString)

/* Find Collation of SQL Server Database */
SELECT DATABASEPROPERTYEX(@DBName, 'Collation')
/* Find Collation of SQL Server Database Table Column */

SELECT name, collation_name
FROM sys.columns
WHERE OBJECT_ID IN (SELECT OBJECT_ID
FROM sys.objects
WHERE type = 'U'
AND name = 'AspNetUsers')
AND name = 'FirstName'

Due to this you need to change database collation before any columns are added or change every column separately. Possible solutions:

因此，您需要在添加任何列或分别更改每个列之前更改数据库排序。可能的解决方式:

1. @MathieuRenda https://*.com/a/42576705/3850405
2. @MathieuRenda https://*.com/a/42576705/3850405

I would put the DbInterception.Add in a class deriving from DbConfiguration or in Application_Start in Global.asax as recommended in the documentation. Note: Wherever you put this code, be careful not to execute DbInterception.Add for the same interceptor more than once, or you'll get additional interceptor instances.

我把DbInterception。添加从DbConfiguration或Application_Start中派生的类。asax在文档中被推荐。注意:无论您将代码放在哪里，都要注意不要执行DbInterception。多次添加相同的拦截器，否则将获得额外的拦截器实例。

public class ApplicationDbConfiguration: DbConfiguration
{
    public ApplicationDbConfiguration()
    {
        DbInterception.Add(new CreateDatabaseCollationInterceptor("Latin1_General_100_CI_AS"));
    }
}

I would also not inherit from the interface but instead use the implementation of DbCommandInterceptor as Microsoft does in their examples.

我也不会继承接口，而是使用DbCommandInterceptor的实现，就像微软在示例中所做的那样。

using System.Data.Common;
using System.Data.Entity.Infrastructure.Interception;
using System.Text.RegularExpressions;

namespace Application.Repositories.EntityFramework
{
    public class CreateDatabaseCollationInterceptor : DbCommandInterceptor
    {
        private readonly string _collation;

        public CreateDatabaseCollationInterceptor(string collation)
        {
            _collation = collation;
        }

        public override void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
        {
            // Works for SQL Server
            if (Regex.IsMatch(command.CommandText, @"^create database \[.*]$"))
            {
                command.CommandText += " COLLATE " + _collation;
            }
        }
    }
}

More information here: https://docs.microsoft.com/en-us/aspnet/mvc/overview/getting-started/getting-started-with-ef-using-mvc/connection-resiliency-and-command-interception-with-the-entity-framework-in-an-asp-net-mvc-application

更多信息:https://docs.microsoft.com/en-us/aspnet/mvc/overview/getting-started/getting-started-with-ef-using-mvc/connection-resiliency-and-command-interception-with-the-entity-framework-in-an-asp-net-mvc-application

2. @steliosalex: https://*.com/a/22895703/3850405. Note that changing every column might not be enough either. You also need to handle metadata and parameters for stored procedure and similar get the collation that the database had when these where created. Changing collation completely requires a create database command with the right collation.

   @steliosalex:https://*.com/a/22895703/3850405。注意，改变每一列可能也不够。您还需要处理存储过程的元数据和参数，类似地得到数据库在创建时所具有的排序。完全更改排序规则需要使用正确的排序规则创建数据库命令。

3. @RahmiAksu https://*.com/a/31119371/3850405 NOTE: This is not a good solution in my opinion but if you use it edit the very first migration. Can't be used if the database is already in production. If you have a seed method the exception Resetting the connection results in a different state than the initial login will be thrown.

   @RahmiAksu https://*.com/a/31119371/3850405注意:在我看来，这不是一个好的解决方案，但是如果您使用它编辑第一次迁移。如果数据库已经在生产中，则不能使用。如果您有一个seed方法，那么在不同状态下重新设置连接结果的异常将会被抛出。

Your Seed SqlException can be solved by using a plain ADO.Net connection, so the context's connection won't be reset. However as mentioned above this will probably cause a lot of errors later.

您的种子SqlException可以通过使用简单的ADO来解决。Net连接，因此上下文的连接不会被重置。但是，如上所述，这可能会在以后引起很多错误。

using (var conn = new SqlConnection(context.Database.Connection.ConnectionString))
{
    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandText = 
            string.Format("ALTER DATABASE [{0}] COLLATE Latin1_General_100_CI_AS",
                context.Database.Connection.Database));
        conn.Open();
        cmd.ExecuteNonQuery();
    }
}

SqlException: Resetting the connection results in a different state than the initial login. The login fails. Login failed for user ''. Cannot continue the execution because the session is in the kill state.

SqlException:重新设置连接的结果与初始登录不同。登录失败。用户登录失败"。不能继续执行，因为会话处于kill状态。

Source:

来源:

https://*.com/a/50400609/3850405

https://*.com/a/50400609/3850405