I would like to explain why you should not use the seed method for this. If you change your database collation after any columns have been added there is a large risk for collation conflicts
like below
Cannot resolve the collation conflict between
"SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_100_CI_AS" in the
equal to operation.
This is due to the fact that if you alter your database with ALTER DATABASE [YourDb] COLLATE [YourCollation]
you will only change the databases collation and not previously created columns.
Example in 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'

Due to this you need to change database collation before any columns are added or change every column separately. Possible solutions:
- @MathieuRenda https://stackoverflow.com/a/42576705/3850405
I would put the DbInterception.Add
in a class deriving from DbConfiguration
or in Application_Start
in Global.asax
as recommended in the documentation. Note: 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"));
}
}
I would also not inherit from the interface but instead use the implementation of DbCommandInterceptor
as Microsoft does in their examples.
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;
}
}
}
}
More information here: https://learn.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
@steliosalex: https://stackoverflow.com/a/22895703/3850405. Note that changing every column might not be enough either. You also need to handle metadata and parameters for stored procedure and similar get the collation that the database had when these where created. Changing collation completely requires a create database command with the right collation.
@RahmiAksu https://stackoverflow.com/a/31119371/3850405 NOTE: This is not a good solution in my opinion but if you use it edit the very first migration. Can't be used if the database is already in production. If you have a seed method the exception Resetting the connection results in a different state than the initial login
will be thrown.
Your Seed SqlException can be solved by using a plain ADO.Net connection, so the context's connection won't be reset. However as mentioned above this will probably cause a lot of errors later.
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: Resetting the connection results in a different state
than the initial login. The login fails. Login failed for user ''.
Cannot continue the execution because the session is in the kill
state.
Source:
https://stackoverflow.com/a/50400609/3850405