Transform Database from MsSQL to MySQL (Net Core EF DbFirst, Scaffold-DbContext, Read Db Schema, Move Users and data, EF SQL trace)
In this page I want describe a one small intermediate task to data transformation from MsSQL to MySQL, in order to move project from .Net Framework to .Net code, from Windows to Linux.
- 1. Source and target SQL servers.
- 2. Database project.
- 3. Why simple SSIS-packages transformation was be impossible?
- 4. Console application project to move data.
- 1. 5. Scaffold-DbContext - read real data structure from old database in SQL Server.
- 6. Define two context - MySQL and MsSQL.
- 7. Add EF SQL tracing.
- 8. Read MS SQL DB structure builded by Scaffold-DbContext.
- 9. Redefine ApplicationDbContext for MySQL from DbContext (not as normally need from IdentityDbContext<ApplicationUser>).
- 10. Moving data.
1. Source and target SQL servers.
I develop project in Windows, but deploy my project in Linux server. Therefore I install in my Windows developers computer MySQL 8.0.15 and in my server I use MariaDB 10.2.31. My Linux version is openSUSE Leap 15.1.
My big fortune that both of this servers is supported by one .Net Core data provider - Pomelo.EntityFrameworkCore.MySql (in this project I use version 3.1.1).
In other side I have old db in MsSQL 2016.
My goal in this small subtask is correct transform data from MsSQL to MySQL with manually check each mistake in datatype and fix mistakes from old project developer.
2. Database project.
Because database definition I need no use in a bunch of subprojects, I define database in separate project. With this project file. Database definition is simple class library project without any startup file. This project has some parts:
- a lot of POCO class with my tables definition
- ConnectionString for MySQL (or MariaDB)
- override OnConfiguring(DbContextOptionsBuilder optionsBuilder) - callback to read conenction string
- ApplicationUser : IdentityUser - definition of additional fields to standard ASP.NET Identity (user definition)
- ApplicationDbContext : IdentityDbContext<ApplicationUser> - definition of list of my tables
Exactly from this project I done "Update-database" command to perform migration. And to pick up this database to .Net core project (with identity, Rolebased authorization and define MySQL Options) I use:
1: using System;
2: using System.Collections.Generic;
3: using System.Linq;
4: using System.Threading.Tasks;
5: using Microsoft.AspNetCore.Builder;
6: using Microsoft.AspNetCore.Components;
7: using Microsoft.AspNetCore.Hosting;
8: using Microsoft.Extensions.Configuration;
9: using Microsoft.Extensions.DependencyInjection;
10: using Microsoft.Extensions.Hosting;
11: using Microsoft.AspNetCore.Authentication;
12: using Microsoft.IdentityModel.Tokens;
13: using Microsoft.AspNetCore.Identity;
14: using Microsoft.EntityFrameworkCore;
15: using Microsoft.AspNetCore.Components.Authorization;
16: using Microsoft.AspNetCore.Http;
17: using Microsoft.AspNetCore.Authentication.Cookies;
18: using Microsoft.AspNetCore.Components.Server.Circuits;
...
63: services.AddDbContext<ApplicationDbContext>(
64: (Microsoft.EntityFrameworkCore.DbContextOptionsBuilder options) =>
65: options.UseMySql(Configuration.GetConnectionString("DefaultConnection"),
66: (Microsoft.EntityFrameworkCore.Infrastructure.MySqlDbContextOptionsBuilder mySqlOption) =>
67: {
68: mySqlOption.CommandTimeout(10);
69: mySqlOption.EnableRetryOnFailure(10);
70: }),
71: ServiceLifetime.Transient, ServiceLifetime.Transient);
72:
73: services.AddIdentity<ApplicationUser, IdentityRole>(options =>
74: {
75: options.Password.RequiredLength = 4;
76: options.Password.RequireUppercase = false;
77: options.Password.RequireLowercase = false;
78: options.Password.RequireUppercase = false;
79: options.Password.RequireNonAlphanumeric = false;
80: options.SignIn.RequireConfirmedEmail = true;
81:
82: }).AddEntityFrameworkStores<ApplicationDbContext>()
83: .AddDefaultTokenProviders();
84:
85: services.AddAuthorization(options =>
86: {
87: options.AddPolicy("RequireAdmin", c => c.RequireRole("Admin"));
88: });
3. Why simple SSIS-packages transformation was be impossible?
Firstly, Because old developer of this project not very accuracy, for example he define UserID with GUID datatype in one table as GUID, but in another table as string (nvarchar(max)).
Secondary, .NET type has different presentation in different SQL server, therefore automaticaly transform exaction database is impossible. For example Boolean .NET Type mapping to MsSQL as as Bool datatype, but in MySQL this is Byte (1) datetype. GUID and other .NET datatype also has different presentation in different SQL server. Therefore simple attempt of transformation raise error and no more.
Thirdly, if you do deploy ASP.NET Core identity to different SQL database (from simple EF code first command "Update-database") you will receive full different User Identity table. For example Roles identity deployed to MsSQL as two fields, but in MySQL as four fields.
And Microsoft costantly and permanently renamed all possible fields it has access - one month pass and field LockoutEndDateUtc was been renamed to LockoutEnd.
4. Console application project to move data.
For transform DB I have created new console application project with that library.
1: <Project Sdk="Microsoft.NET.Sdk">
2:
3: <PropertyGroup>
4: <OutputType>Exe</OutputType>
5: <TargetFramework>netcoreapp3.1</TargetFramework>
6: </PropertyGroup>
7:
8: <ItemGroup>
9: <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="3.1.6" />
10: <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="3.1.6">
11: <PrivateAssets>all</PrivateAssets>
12: <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
13: </PackageReference>
14: <PackageReference Include="Microsoft.Extensions.Logging.Debug" Version="3.1.6" />
15: <PackageReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Design" Version="3.1.3" />
16: <PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="3.1.2" />
17: <PackageReference Include="System.Configuration.ConfigurationManager" Version="4.7.0" />
18: </ItemGroup>
19:
20: <ItemGroup>
21: <ProjectReference Include="MyDb.csproj" />
22: </ItemGroup>
23:
24: <ItemGroup>
25: <None Update="appsettings.json">
26: <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
27: </None>
28: </ItemGroup>
29:
30: </Project>
5. Scaffold-DbContext - read real data structure from old database in SQL Server.
Scaffold-DbContext "Server=(localdb)\mssqllocaldb;Database=Docker;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -ContextDir MsSQLContext -Context MsSQLContext
Pay attention - Microsoft developers are so degenerate then not understand how to create definition of DB structure in VB.NET, their intellect low than zero, I has read MS chatting for this themes and one MS degenerate wrote to second MS degenerate - "I don't understand how is possible to write lambda expression in VB." This two dumb aborted child use this project to see how is possible to write lambda expression on VB and what difference between VB and C#.
Second points of your pay attention - MS degenerates not read DB structure even for MS SQL server, for example, in Ms Servre I have one view, based on table. This view has no have key table in result. Therefore key field was inaccessible.
However I receive main part of my MS SQL db structure:
6. Define two context - MySQL and MsSQL.
Than I define two context - for old database MsSQL and my new database in MySQL.
24: static void Main(string[] args)
25: {
26: var MySQL = new AspNetIdentity.ApplicationDbContext();
27: var MsSQL = new MsSQLContext.MsSQLContext();
28:
After scaffording (by Scaffold-DbContext) I receive configured MS provider and common structure of my MS SQL database (this is EF Database First db pattern).
But there are two big problem:
- Received MS DB structure was incorrect - see above about scaffording View.
- And second big problem - ASP user identity deployed to MSSQL and MySQL by different way - see above
Therefore for continue this transformation project need two very important additional steps.
7. Add EF SQL tracing.
For tracing by EF SQL command I add to project package Microsoft.Extensions.Logging.Debug and define it as static in may main module.
22: public static readonly ILoggerFactory _loggerFactory
23: = LoggerFactory.Create(builder => builder.AddDebug().AddFilter((category, level) => level == LogLevel.Information && !category.EndsWith("Connection")));
And after that I add tracing to my MySQL and MsSQL context.
1: using System;
2: using System.Collections.Generic;
3: using System.ComponentModel.DataAnnotations.Schema;
4: using System.IO;
5: using System.Linq;
6: using System.Threading.Tasks;
7: using Microsoft.AspNetCore.Identity;
8: using Microsoft.AspNetCore.Identity.EntityFrameworkCore;
9: using Microsoft.EntityFrameworkCore;
10: using Microsoft.Extensions.Configuration;
11: using Models;
12:
13: namespace AspNetIdentity
14: {
15: public partial class ApplicationDbContext
16: {
17: protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
18: {
19: if (!optionsBuilder.IsConfigured)
20: {
21: IConfigurationRoot configuration = new ConfigurationBuilder()
22: .SetBasePath(Directory.GetCurrentDirectory())
23: .AddJsonFile("appsettings.json")
24: .Build();
25:
26: var connectionString = configuration.GetConnectionString("DefaultConnection");
27: optionsBuilder.UseMySql(connectionString).UseLoggerFactory(Move.Program._loggerFactory);
28: }
29: }
30:
31: protected override void OnModelCreating(ModelBuilder modelBuilder)
32: {
33: base.OnModelCreating(modelBuilder);
34: }
35:
36: }
37: }
Only when this step was done you can see SQL command in immediate window. As you can see this is different pattern than in ASP.NET Core container with dependency injection (for example see that pattern in this page).
8. Read MS SQL DB structure builded by Scaffold-DbContext.
Because EF DB working incorrectly even with MS SQL server, next mandatory step is including reading DB structure, without this step any searching mistakes is impossible.
27: var MsSQL = new MsSQLContext.MsSQLContext();
28:
29: var entityType = MsSQL.Model.FindEntityType("MsSQLModels.ShareOwners");
30:
31: // Table info
32: var tableName = entityType.GetTableName();
33: var tableSchema = entityType.GetSchema();
34:
35: // Column info
36: foreach (var property in entityType.GetProperties())
37: {
38: var columnName = property.GetColumnName();
39: var columnType = property.GetColumnType();
40: Debug.Print($"{ tableName }, {tableSchema },{columnName},{columnType} ");
41: };
You will receive the same information about fields.
But pay attention you can use CLR names of EF Poco class. You can receive it bu this way.
9. Redefine ApplicationDbContext for MySQL from DbContext (not as normally need from IdentityDbContext<ApplicationUser>).
This is last key point to move data from MS SQL to My SQL by correct way. Because .NET definition of ASP.NET user is different in MySQL and MySQL, you can read and write raw data.
In my shared project with DB definition I define ASP.NET user databases as inheritance od IdentityDbContext
But in transformation project I inherit database definition from DbContext
This pattern allow me manually changed each fields (by name and type) during a transformation ASP.NET Idntity (and other data) from MS SQL to My SQL.
43: MsSQL.AspNetRoles.OrderBy(row => row.Id).ForEachAsync(row =>
44: {
45: Console.WriteLine($"{row.Id}");
46: MySQL.AspNetRoles.Add(new AspNetIdentity.AspNetRoles
47: {
48: Id = row.Id,
49: Name = row.Name,
50: });
51: MySQL.SaveChanges();
52: }).GetAwaiter().GetResult();
53:
54:
55: MsSQL.AspNetUsers.OrderBy(row => row.Id).ForEachAsync(row =>
56: {
57: Console.WriteLine($"{row.Id}");
58: MySQL.AspNetUsers.Add(new AspNetIdentity.AspNetUsers
59: {
60: Id = row.Id,
61: DiscordId = row.DiscordId,
62: DiscordEnabled = row.DiscordEnabled,
63: DiscordServerId = row.DiscordServerId,
64: IsGoogleAuthenticatorEnabled = row.IsGoogleAuthenticatorEnabled,
65: GoogleAuthenticatorSecretKey = row.GoogleAuthenticatorSecretKey,
66: Ipaddress = row.Ipaddress,
67: UserAgent = row.UserAgent,
68: ResetDone = row.ResetDone,
69: Apikey = row.Apikey,
70: Email = row.Email,
71: EmailConfirmed = row.EmailConfirmed,
72: PasswordHash = row.PasswordHash,
73: SecurityStamp = row.SecurityStamp,
74: PhoneNumber = row.PhoneNumber,
75: PhoneNumberConfirmed = row.PhoneNumberConfirmed,
76: TwoFactorEnabled = row.TwoFactorEnabled,
77: LockoutEnd = row.LockoutEndDateUtc,
78: LockoutEnabled = row.LockoutEnabled,
79: AccessFailedCount = row.AccessFailedCount,
80: UserName = row.UserName
81:
82: });
83: MySQL.SaveChanges();
84: }).GetAwaiter().GetResult();
85:
86: MsSQL.AspNetUserClaims.OrderBy(row => row.UserId).ForEachAsync(row =>
87: {
88: Console.WriteLine($"{row.UserId}");
89: MySQL.AspNetUserClaims.Add(new AspNetIdentity.AspNetUserClaims
90: {
91: Id = row.Id,
92: UserId = row.UserId,
93: ClaimType = row.ClaimType,
94: ClaimValue = row.ClaimValue
95:
96: });
97: MySQL.SaveChanges();
98: }).GetAwaiter().GetResult();
99:
10. Moving data.
If all step was be correct, relax and enjoy. In my developer computer about 40 record per second reading from MsSQL, transform to .NET type and than write to MySQL:
- with overcome all bugs from MS in order to build db Structure by Scaffold-DbContext,
- with fix MS renamed fields in ASP.NET user identity,
- with fix all bugs of old developers (like define GUID fields as nvarchar(max)),
- with transform data from MS SQL type to My SQL type (like boolean to byte(1))
)
| <SITEMAP> <MVC> <ASP> <NET> <DATA> <KIOSK> <FLEX> <SQL> <NOTES> <LINUX> <MONO> <FREEWARE> <DOCS> <ENG> <CHAT ME> <ABOUT ME> < THANKS ME> |