Migrate DB Context

Migrate from MySQL to MsSQL

Install required package

• Install Package Microsoft.EntityFrameworkCore.SqlServer
• Install Package Microsoft.EntityFrameworkCore
• Install Package Microsoft.EntityFrameworkCore.Tools
• Install Package Microsoft.EntityFrameworkCore.Design

Add SQL Server DB Context

public class SqlServerDbContext : DbContext
{
    private const string connectionString = @"Server=.;Database=DB1;User Id=sa;Password=***;TrustServerCertificate=True";

    public SqlServerDbContext(DbContextOptions<SqlServerDbContext> options) : base(options) { }

    public DbSet<Arbeitgeber> Arbeitgeber { get; set; }
    public DbSet<Arbeitgeberstammdaten> Arbeitgeberstammdaten { get; set; }
    public DbSet<Beratung> Beratungen { get; set; }
    public DbSet<GehaltsExtra> GehaltsExtras { get; set; }
    public DbSet<Person> Personen { get; set; }
    public DbSet<Profil> Profile { get; set; }
    public DbSet<ProfilGehaltsExtra> ProfilGehaltsExtras { get; set; }
    public DbSet<ProfilVorsorgevertrag> ProfilVorsorgevertraege { get; set; }
    public DbSet<Vorsorgevertrag> Vorsorgevertraege { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
            optionsBuilder.UseSqlServer(connectionString);
        }
    }
}

Add MySQL DB Context

public class MySqlDbContext : DbContext
{
    private const string connectionString = @"Server=server.com;port=3306;Database=DB2;user=user;password=***";
    private readonly MariaDbServerVersion serverVersion = new(new Version(10, 5, 16));
    public DbSet<Arbeitgeber> Arbeitgeber { get; set; }
    public DbSet<Arbeitgeberstammdaten> Arbeitgeberstammdaten { get; set; }
    public DbSet<Beratung> Beratungen { get; set; }
    public DbSet<GehaltsExtra> GehaltsExtras { get; set; }
    public DbSet<Person> Personen { get; set; }
    public DbSet<Profil> Profile { get; set; }
    public DbSet<ProfilGehaltsExtra> ProfilGehaltsExtras { get; set; }
    public DbSet<ProfilVorsorgevertrag> ProfilVorsorgevertraege { get; set; }
    public DbSet<Vorsorgevertrag> Vorsorgevertraege { get; set; }
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseMySql(connectionString, serverVersion);
    }
}

Add migration service

public class MigrationToSqlServerService
{
    private readonly MySqlDbContext _mySqlDbContext;
    private readonly SqlServerDbContext _sqlServerDbContext;

    public MigrationToSqlServerService()
    {
        _mySqlDbContext = new MySqlDbContext();

        var sqlServerOptionsBuilder = new DbContextOptionsBuilder<SqlServerDbContext>();
        sqlServerOptionsBuilder.UseSqlServer("Server=.;Database=SimsaTest;User Id=sa;Password=QAZqaz@123;TrustServerCertificate=True");
        _sqlServerDbContext = new SqlServerDbContext(sqlServerOptionsBuilder.Options);
    }

    //public MigrationToSqlServerService(SimsaDb mySqlDbContext, SqlServerDbContext sqlServerDbContext)
    //{
    //    _mySqlDbContext = mySqlDbContext;
    //    _sqlServerDbContext = sqlServerDbContext;
    //}

    public async Task MigrateDataAsync()
    {
        await MigrateTableAsync<Arbeitgeber>(_mySqlDbContext.Arbeitgeber, _sqlServerDbContext.Arbeitgeber);
        await MigrateTableAsync<Arbeitgeberstammdaten>(_mySqlDbContext.Arbeitgeberstammdaten, _sqlServerDbContext.Arbeitgeberstammdaten);
        await MigrateTableAsync<Beratung>(_mySqlDbContext.Beratungen, _sqlServerDbContext.Beratungen);
        await MigrateTableAsync<GehaltsExtra>(_mySqlDbContext.GehaltsExtras, _sqlServerDbContext.GehaltsExtras);
        await MigrateTableAsync<Person>(_mySqlDbContext.Personen, _sqlServerDbContext.Personen);
        await MigrateTableAsync<Profil>(_mySqlDbContext.Profile, _sqlServerDbContext.Profile);
        await MigrateTableAsync<ProfilGehaltsExtra>(_mySqlDbContext.ProfilGehaltsExtras, _sqlServerDbContext.ProfilGehaltsExtras);
        await MigrateTableAsync<ProfilVorsorgevertrag>(_mySqlDbContext.ProfilVorsorgevertraege, _sqlServerDbContext.ProfilVorsorgevertraege);
        await MigrateTableAsync<Vorsorgevertrag>(_mySqlDbContext.Vorsorgevertraege, _sqlServerDbContext.Vorsorgevertraege);
    }

    private async Task MigrateTableAsync<TEntity>(DbSet<TEntity> source, DbSet<TEntity> destination) where TEntity : class
    {
        var tableName = _sqlServerDbContext.Model.FindEntityType(typeof(TEntity)).GetTableName();
        var primaryKeyProperty = _sqlServerDbContext.Model.FindEntityType(typeof(TEntity)).FindPrimaryKey().Properties.FirstOrDefault();

        var hasIdentityColumn = primaryKeyProperty != null && primaryKeyProperty.ValueGenerated == ValueGenerated.OnAdd;
        var isUniqueIdentifier = primaryKeyProperty != null && primaryKeyProperty.ClrType == typeof(Guid);

        await using var transaction = await _sqlServerDbContext.Database.BeginTransactionAsync();
        try
        {
            if (hasIdentityColumn && !isUniqueIdentifier)
            {
                await _sqlServerDbContext.Database.ExecuteSqlRawAsync($"SET IDENTITY_INSERT {tableName} ON");
            }

            var data = await source.AsNoTracking().ToListAsync();
            await destination.AddRangeAsync(data);
            await _sqlServerDbContext.SaveChangesAsync();

            if (hasIdentityColumn && !isUniqueIdentifier)
            {
                await _sqlServerDbContext.Database.ExecuteSqlRawAsync($"SET IDENTITY_INSERT {tableName} OFF");
            }

            await transaction.CommitAsync();
        }
        catch (Exception ex)
        {
            await transaction.RollbackAsync();
            throw;
        }
    }

• Add DBContext in program.cs (to run add-migration command)

builder.Services.AddDbContext<SqlServerDbContext>(options =>
options.UseSqlServer("Server=.;Database=DB2;User Id=sa;Password=***;TrustServerCertificate=True"));

• Create SQL Server DB with the same name in ConnectionString

• Add migration with a specific DBContext When you have more than one context

• Run code in Package Manager Console

  Add-Migration initialDB -Context SqlServerDbContext
  

• Update database with a specific DBContext

  Update-Database -Context SqlServerDbContext
  

• Run this code to apply changes

  var m = new MigrationToSqlServerService().MigrateDataAsync();