CODEWITHSUNDEEP
asp.net-mvcef-code-firstentity-framework-coreef-core-2.0entity-framework-core-migrations
Ehsan Mirsaeedi
Ehsan Mirsaeedi

Reputation: 7592

SQL Server Collations In EF Core

How can I specify the collation of a column, a table, or a database by using annotation or Fluent API? I know there are some clean ways for MySql Provider. However, I couldn't find any way for SQL Server other than executing a raw SQL command.

Upvotes: 13

Views: 7164

Answers (3)

Mikola Akbal
Mikola Akbal

Reputation: 161

UseCollation() for database and column levels was added to EF Core 5.

Upvotes: 4

Mahdi Abyaznezhad
Mahdi Abyaznezhad

Reputation: 522

Although it is possible to change the collation of the database after creation (while there are some data in its tables), it is better to set the collation of the database before creating any tables. So in EF core for creating database initially before creating any table:

  1. It is better to make sure you have not added any DbSet in the DbContext file of your project.
  2. Open the Nuget Package Manager
  3. Run this code
PM> add-migration CreateDatabase
  1. Right now an empty Up and Down method is created in a migration file like this:
        protected override void Up(MigrationBuilder migrationBuilder)
        {
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
        }
  1. Write this code in the Up method. Make sure your collation Name is correct.
    migrationBuilder.AlterDatabase("CollationName");
    //or
    migrationBuilder.Sql("EXEC('ALTER DATABASE [POMADB] COLLATE     CollationName ');", suppressTransaction: true);
  1. Run this code
    PM> update-database

I have tested this code in .NET Core 5, Visual Studio 2019, Ef core 5.0.9

For further information look at this link Ability to specify database collation

Upvotes: 1

Tolbxela
Tolbxela

Reputation: 5183

Looks like it will not be done at all:

Here is the closed Issue in aspnet/EntityFrameworkCore: CodeFirst: Set column collation

We discussed and concluded that setting different collations per column isn't a very common requirement and it's probably not something we would make first class in EF. This would be a good candidate for using annotations to define additional metadata and then extending our SQL generators to process those and specify collation.

The only solution seems to be this one: Entity Framework Code First - Change Table Column Collation

... you can create custom database initializer and execute ALTER TABLE command.

Upvotes: 2

Related Questions