sql-server - utf8 - select collate sql server

Establecer la intercalación de la base de datos en el inicializador de código de Entity Framework

Question

Quiero establecer la intercalación predeterminada para una base de datos, cuando Entity Framework Code First lo cree.

He intentado lo siguiente:

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");
    }
}

Esto parece funcionar correctamente cuando SQL Server ya está configurado con la misma intercalación predeterminada Latin1_General_CI_AS.

Pero si especifico una intercalación diferente , digamos SQL_Latin1_General_CP1_CI_AS esto falla con el error,

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

¿Alguien puede aconsejar cómo puedo establecer la colación por favor?

Answer 1

Solución con un interceptor de comandos.

Definitivamente es posible, aunque es un poco pirateado. Puede modificar el comando CREAR BASE DE DATOS con un interceptor de comandos. Interceptará todos los comandos enviados a la base de datos, reconocerá el comando de creación de la base de datos basándose en una expresión regular y modificará el texto del comando con su intercalación.

Antes de la creación de la base de datos

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

El 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) { }
}

Observaciones

Dado que la base de datos se crea con la intercalación correcta desde el principio, todas las columnas heredarán automáticamente esa intercalación y no tendrá que ALTERARLAS después.

Tenga en cuenta que afectará cualquier creación posterior de la base de datos dentro del dominio de la aplicación. Por lo tanto, es posible que desee eliminar el interceptor después de crear la base de datos.

Answer 2

EF 5 ahora admite la creación de tablas faltantes en una base de datos existente con Code First, por lo que puede crear una base de datos vacía y establecer la intercalación correcta, antes de ejecutar un CF en ella.

Answer 3

He tenido el mismo problema hace un tiempo. Soluciones posibles:

1. Parece que EF crea la base de datos utilizando la intercalación predeterminada del servidor, por lo que una cosa que podría hacer es cambiar eso.
2. No puede cambiar la intercalación de la base de datos dentro del método Seed() , pero puede cambiar la intercalación de columnas individuales para una tabla (NOTA: no existe tal cosa como la intercalación de tablas, se relaciona con la columna en una tabla). Tendrás que cambiar la colación de cada columna por separado.
3. Si está utilizando migraciones, podría alterar las colaciones de columnas de la tabla dentro de su método Up() .

Como está utilizando el método Seed() , sugeriría lo siguiente (modificar según corresponda) dentro del método Seed() :

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

Espero que ayude.

Answer 4

Me gustaría explicar por qué no debería usar el método de semilla para esto. Si cambia la intercalación de la base de datos después de agregar columnas, existe un gran riesgo de collation conflicts como se muestra a continuación.

No se puede resolver el conflicto de intercalación entre "SQL_Latin1_General_CP1_CI_AS" y "Latin1_General_100_CI_AS" en la operación igual a.

Esto se debe al hecho de que si modifica su base de datos con ALTER DATABASE [YourDb] COLLATE [YourCollation] solo cambiará la intercalación de las bases de datos y no las columnas creadas previamente.

Ejemplo en 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''

Debido a esto, debe cambiar la intercalación de la base de datos antes de agregar cualquier columna o cambiar cada columna por separado. Soluciones posibles:

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

Yo pondría DbInterception.Add en una clase derivada de DbConfiguration o en Application_Start en Global.asax como se recomienda en la documentación. Nota: 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.

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

Tampoco heredaría de la interfaz, sino que usaría la implementación de DbCommandInterceptor como lo hace Microsoft en sus ejemplos.

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;
            }
        }
    }
}

Más información aquí: 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 . Tenga en cuenta que cambiar cada columna podría no ser suficiente tampoco. También debe manejar los metadatos y los parámetros para el procedimiento almacenado y obtener la recopilación que tenía la base de datos cuando se crearon. Cambiar la intercalación completamente requiere un comando crear base de datos con la intercalación correcta .

3. @RahmiAksu https://.com/a/31119371/3850405 NOTA: Esta no es una buena solución en mi opinión, pero si la usa, edite la primera migración. No se puede utilizar si la base de datos ya está en producción. Si tiene un método Resetting the connection results in a different state than the initial login , se lanzará la excepción Al Resetting the connection results in a different state than the initial login .

Su Seed SqlException se puede resolver utilizando una conexión ADO.Net simple, por lo que la conexión del contexto no se restablecerá. Sin embargo, como se mencionó anteriormente, esto probablemente causará muchos errores más adelante.

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: restablecer los resultados de la conexión en un estado diferente al inicio de sesión inicial. El inicio de sesión falla. Error de usuario ''''. No se puede continuar la ejecución porque la sesión está en el estado de eliminación.

Fuente:

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

Answer 5

Mi solución con EFCore fue derivar de SqlServerMigrationsSqlGenerator y anular Generate (SqlServerCreateDatabaseOperation, IModel, MigrationCommandListBuilder)

    internal class CustomSqlServerMigrationsSqlGenerator : SqlServerMigrationsSqlGenerator
    {
        internal const string DatabaseCollationName = "SQL_Latin1_General_CP1_CI_AI";

        public CustomSqlServerMigrationsSqlGenerator(
            MigrationsSqlGeneratorDependencies dependencies,
            IMigrationsAnnotationProvider migrationsAnnotations)
        : base(dependencies, migrationsAnnotations)
        {
        }

        protected override void Generate(
            SqlServerCreateDatabaseOperation operation,
            IModel model,
            MigrationCommandListBuilder builder)
        {
            base.Generate(operation, model, builder);

            if (DatabaseCollationName != null)
            {
                builder
                    .Append("ALTER DATABASE ")
                    .Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name))
                    .Append(" COLLATE ")
                    .Append(DatabaseCollationName)
                    .AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator)
                    .EndCommand(suppressTransaction: true);
            }
        }
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        base.OnConfiguring(optionsBuilder);
        optionsBuilder.ReplaceService<IMigrationsSqlGenerator, CustomSqlServerMigrationsSqlGenerator>();
    }

luego lo usó en el DbContext reemplazando el servicio IMigrationsSqlGenerator

public class MyDbContext : DbContext
{
    //...

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        base.OnConfiguring(optionsBuilder);
        optionsBuilder.ReplaceService<IMigrationsSqlGenerator, CustomSqlServerMigrationsSqlGenerator>();
    }

    //...
}

Answer 6

Pude cambiar la intercalación con una migración personalizada (EF6). Tengo las migraciones automáticas habilitadas. Necesitas borrar tu DB primero.

1. Cree el código de migración escribiendo Add-Migration [YourCustomMigration] en la consola de Package Manager. ( Código de primeras migraciones )
2. El primer paso debe crear su clase de migración con el código de creación del modelo actual en el reemplazo Up (). Agregue su código ALTER DATABASE ANTES de los códigos de creación de tablas para que se creen utilizando la intercalación de base de datos que desea. Además, tenga en cuenta el indicador de suppressTransaction transacción:

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

Cada comando de update-database emitido desde entonces crea una nueva clase de migración. Todos los códigos de migración se ejecutan en orden.

Answer 7

Simplemente no es posible utilizar las versiones actuales de EF (EF6). Sin embargo, al menos EF6 + ahora puede trabajar con una base de datos ya existente. Hemos cambiado nuestro escenario de implementación de manera que la base de datos ya está creada por nuestro script de implementación (incluida la intercalación predeterminada) y dejamos que EF6 trabaje con la base de datos existente (utilizando la intercalación predeterminada correcta).

Si absolutamente tiene que crear la base de datos dentro de su código y no puede usar otra cosa que no sea EF (por ejemplo, no puede crear la base de datos utilizando ADO.NET), entonces tiene que buscar la respuesta de seliosalex. Es la única solución que encontramos, sin embargo, vea mi comentario, es mucho trabajo hacerlo bien.