Code-first, setting database collation

Question

Is there any way to configure what collation to use when EF creates the database?

Or is there some kind of hook to set the collation after the database is created but before the tables are created?

Entity Framework 6.1.1
MS SQL 2012

possible duplicate of [How to change/set collation in EF Code first](http://stackoverflow.com/questions/13433257/how-to-change-set-collation-in-ef-code-first) — Yuliam Chandra, Aug 20 '14 at 10:17
I have seen the thread, but was hoping that a cleaner solution was created two major releases of EF later :-( — Smoller, Aug 20 '14 at 10:30
there isn't, check [here](http://msdn.microsoft.com/en-us/data/jj574253.aspx), no update about collation, you can use custom initializer and have create database query that specifies the collation — Yuliam Chandra, Aug 20 '14 at 10:32
look at here [How to change/set collation in EF code first][1] [1]:http://stackoverflow.com/questions/13433257/how-to-change-set-collation-in-ef-code-first — Dmitriy Bykov, Oct 13 '15 at 11:48
Possible duplicate of [Set database collation in Entity Framework Code-First Initializer](http://stackoverflow.com/questions/12054930/set-database-collation-in-entity-framework-code-first-initializer) — Mathieu Renda, Mar 10 '17 at 23:06

score 2 · Answer 1 · answered May 07 '15 at 13:45

I solved the issue by creating the DB myself. The base class is then creating the tables into the empty DB:

    public class MyInitializer : CreateDatabaseIfNotExists<MasterDataModel>
    {
        public override void InitializeDatabase(MasterDataModel context)
        {
            if(!context.Database.Exists())
            {
                using (SqlConnection connection = new SqlConnection("YourConnectionString"))
                {
                    connection.Open();
                    using(SqlCommand command = new SqlCommand(string.Format("CREATE DATABASE {0} COLLATE Latin1_General_CI_AS", "NameOfDatabase"), connection))
                    {
                        command.ExecuteNonQuery();
                    }
                }

                SqlConnection.ClearAllPools();
             }

             base.InitializeDatabase(context);
        }
    }

score 1 · Answer 2 · answered Mar 27 '16 at 00:41

The issue could be solved by opening dedicated connection to the database and execute the alter sql command through it.

Notice how we use the connection string passed from the framework to open the new connection. Thank for @huer12 as I used his code.

public class DBInitializer<T > : CreateDatabaseIfNotExists<T> where T : DbContext
{
    void SetDatabaseCollation(DbContext context)
    {
        using (SqlConnection connection = new SqlConnection(context.Database.Connection.ConnectionString))
        {
            connection.Open();                
            using (SqlCommand command = new SqlCommand(string.Format("ALTER DATABASE [{0}] COLLATE Latin1_General_CI_AS", context.Database.Connection.Database), connection))
            {
                command.ExecuteNonQuery();
            }
         SqlConnection.ClearAllPools();
         connection.Close();
        }
    }
    protected override void Seed(T context)
    {
        SetDatabaseCollation(context);
    }

}  

public class MyDbContext : MyDbContext
{
    public MyDbContext() : base("ConnectionString", throwIfV1Schema: false)
    {
        Database.SetInitializer(new DBInitializer<MyDbContext>());
        if(!Database.Exists())
        {
            Database.Initialize(true);
         }
     } 
}

Code-first, setting database collation

2 Answers2

Linked