Set database collation in Entity Framework Code-First Initializer

by

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.

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.

More Related Contents:

  1. Entity Framework Core 2.0: How to configure abstract base class once
  2. How do I programmatically set the connection string for Entity-Framework Code-First?
  3. Create Stored Procedures using Entity Framework Code First?
  4. Implementing one-to-zero-or-one relation in SQL Server
  5. SQL Server – error when creating stored procedure
  6. SQL Server insert if not exists best practice [closed]
  7. Are GUID collisions possible?
  8. SQL Server Escape an Underscore
  9. Does a foreign key automatically create an index?
  10. best way to convert and validate a date string
  11. Why use the INCLUDE clause when creating an index?
  12. Why do table names in SQL Server start with “dbo”?
  13. Add alternating row color to SQL Server Reporting services report
  14. LIKE vs CONTAINS on SQL Server
  15. How do I enable MSDTC on SQL Server?
  16. TSQL Try / Catch within Transaction or vice versa?
  17. Is there any difference between DECIMAL and NUMERIC in SQL Server?
  18. SQL Server history table – populate through SP or Trigger?
  19. How to load an XML file into a database using an SSIS package?
  20. TSQL: Create a view that accesses multiple databases
  21. Check if a temporary table exists and delete if it exists before creating a temporary table
  22. What is the sql connection string I need to use to access localhost\SQLEXPRESS with Windows Authentication or SQL Authentication?
  23. SQL Error with Order By in Subquery
  24. Executing set of SQL queries using batch file?
  25. How to change default language for SQL Server?
  26. Removing the remembered login and password list in SQL Server Management Studio
  27. SQL Server equivalent to Oracle’s CREATE OR REPLACE VIEW
  28. In SQL Server, how can I find everywhere a column is referenced?
  29. Using Full-Text Search in SQL Server 2008 across multiple tables, columns
  30. Convert float into varchar in SQL Server without scientific notation

Leave a Comment