Efcore: Ability to specify database collation

[valamelkor]

There is no way to do this at the moment. Database is created with default settings. It is not an issue on most cases, but there is a plenty of specific ones. For example, I have different collation on production server db, then on development machine.

After some research I've come up with workaround. Pretty ugly so far, but at least working

In Migrations\00000000000000_CreateIdentitySchema.cs add

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.Sql($"DECLARE @dbname sysname = db_name(); " +
    "EXEC('ALTER DATABASE [' + @dbname + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE');" +
    "EXEC('ALTER DATABASE [' + @dbname + '] COLLATE Cyrillic_General_CI_AS');" +
    "EXEC('ALTER DATABASE [' + @dbname + '] SET MULTI_USER') ", suppressTransaction: true);
    ...
}

It is ok. But the connection is reset after execution of this first migration so I have to run in separately.
I've come up with the following script (resetdb.cmd):

dotnet ef database update 00000000000000_CreateIdentitySchema
dotnet ef database update

So, there should be a proper way to do such things.

See also #6565

[rowanmiller]

We agree this would be useful. It's lower priority than the other features we are working on now, so moving to the backlog to look at in a future release.

[Ciantic]

EnsureCreatedAsync there, now this item can be found with GitHub search

[seriouz]

I have a database with some columns collations set to be case sensitive. Now EF build its query with a temp table, but this temp table does not have the collation on the column so on the insert the database server throws an exception.
Until there is a work-around i can not use ef because the regarding columns contain (case sensitive) guids.

[divega]

@seriouz thanks for reporting this. I have created a new issue at #7172. Please add more relevant details there.

[javiercampos]

Has anyone taken a look into this since Dec 2016? I'm using a different (albeit similar) workaround than the OP so I don't have to apply migrations independently (I just open a connection on the Up method and send the SQL directly) but it's definitely pretty ugly:

[DbContext(typeof(MyDbContext))]
[Migration("00000000000000_SetupDatabase")]
public class SetupDatabase : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // HACK: there must be a better way
        using (var connection = new SqlConnection(MyContextOptions.ConnectionString))
        {
            connection.Open();
            using (var command =
                new SqlCommand(
                    $"ALTER DATABASE [{MyDatabaseName}] COLLATE {MyDefaultCollation}",
                    connection))
                command.ExecuteNonQuery();
            SqlConnection.ClearAllPools();
            connection.Close();
        }
    }
}

By chance (and I say by chance because as I understand, the MigrationBuilder only builds up the instructions that will be executed later and this could be done at any point in time) when the Up on this first migration is called, the database is already created but no tables have been created yet.

Still, having native support on the SQL Server provider for this would be great... something like:

migrationBuilder.AlterDatabase().Annotation("COLLATE", "WhateverCollation_CI_AI");

Should work

[morilon]

Do we have an update on this? I'll try this workaround but it would be great to have something configurable to do so.

Something more stylish as

modelBuilder.Collation("Whatever_CI_AI")

at least something that disables/enables CI/AI.

modelBuilder.IsCollationSensitive(false) or something.

[RedDeathGitHub]

Issue is open since 2016, any plans to fix this?

[ajcvickers]

@RedDeathGitHub This issue is in the Backlog milestone. This means that it is not going to happen for the 3.0 release. We will re-assess the backlog following the 3.0 release and consider this item at that time. However, keep in mind that there are many other high priority features with which it will be competing for resources.

[RedDeathGitHub]

@ajcvickers Understood - thank you for the info.

[morilon]

Well, I created an extension using some examples I found and I've come up with this.

    public static class MigrationBuilderExtensions
    {
        public static void SetDatabaseCollationToInsensitive(this MigrationBuilder builder)
        {
            builder.Sql("DECLARE @dbname sysname = db_name(); " +
                        "EXEC('ALTER DATABASE [' + @dbname + '] COLLATE SQL_Latin1_General_CP1_CI_AI');", true);
        }
    }

And after creating your first migration just add the information on the top.

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.SetDatabaseCollationToInsensitive();
}

It's working by just using Update-Database, works for me for now.

[RedDeathGitHub]

Before I was creating the Db manually earlier, but I run into "interesting" issue where just after creation - the Db did not accept connections yet. So migrations would try to create an existing Db.
Mostly an issue with testing and local environments but still a nasty one.

This looks interesting, I'll try it, thanks :).

[smitpatel]

@RedDeathGitHub - That is SqlServer behavior. After creating brand new database, it does not allow you to connect to it immediately. (Log in failed for user, which EF core uses as clue to non-existent database). We have also seen the same issue in our tests and have wait time and retry logic for that.

[cmanoliu]

Hello everyone, for the moment, my solution is to derive from the SqlServerMigrationsSqlGenerator and override 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>();
    }

then used it in the DbContext by replacing the IMigrationsSqlGenerator service

public class MyDbContext : DbContext
{
    //...

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

    //...
}

[ajcvickers]

Consider also character sets--see #15360

[ajcvickers]

See also scenario in #15776

[ajcvickers]

Also see high-level collations issue #19866

[roji]

Putting into the 5.0 milestone as per our design discussion.

[roji]

Note: am using this issue to track database collation (both at creation time and later alteration). For other database-creation settings, please open separate issues for the specific feature you're missing.