Last Updated on 2016-09-05.
[:en]Starting with a new web project, I first chose this combination of recently available packages:
- Visual Studio 2015 Update 3
- ASP.NET Core MVC Project with Entity Framework 7
- It offers great options for using Nuget or Bower, keeping packages like bootstrap, jquery or even foreign Github project sources up to date
- .NET Core seems to be quite flexible regarding server OS
- MySQL EntityFramework for .NET Core
- (Existing remote MySQL DB server)
After some testing, I realized there are a lot of disadvantages in this combination – at least at this moment:
- EF7 does not offer any EDMX tools (e.g. the Model designer, which was a convenient way to update DB schema changes with one click, is missing).
- MySQL.EntityFrameworkCore does not offer building models by reverse-engineering (DB-first) at the moment. No matter which MySQL assembly you choose, you get an error like “Unable to find attribut DesignTimeProviderServicesAttribute in assembly”. Unfortunately, this feature is also missing in the alternative provider of SapientGuardian. In addition, the syntax of the scaffold command is not correct in the current documentation (see below).
- Database views need special treatment, they are not created in the project by default.
- .NET Core is missing a lot of useful server-side features, like System.Drawing. Only a minimized version is available at the moment. If you need this, you have to convert your project e.g. to .NET Framework 4.6.1 (OS dependent) or choose alternatives like this one.
- Command-line tools’ syntax seems to change frequently with updates, e.g. “dnx”, “dotnet”, “dotnet ef”, “scaffold-dbcontext”, …
Some of those points were quite frustating, but I didn’t want to start a new project with old components. Especially the missing DB reverse engineering feature for MySQL was a pity (the existing DB structure is huge).
So I made a compromise and changed my remote test database to MS SQL Server Express 2016 (in the hope of after some months there will be full-supported MySQL modules…).
Microsoft offers a great Management Studio and a tool to import your existing MySQL DB, which worked without any errors in my case. You only have to install MySQL ODBC Connector on the server system (or where you migrate). Use the 32-bit and 64-bit connector to be safe.
Migration process:
- Connect to MySQL server
- Connect to MS SQL server
- Convert Schema
- Right-click your new SQL server schema and select “synchronize database”
- Migrate Data
- Notice errors, if any
For remote usage of SQL Server, check the following:
- Firewall: Enable one incoming TCP and UDP port, e.g. 1433
- SQL Server Configuration Manager
- Enable TCP/IP protocol
- Disable all entries containing dynamic ports (should be really empty, not 0) and set your static port (1433)
- Enable and Start Browser Service
- SQL Server Management Studio
- Enable “Accept remote connections” (should be default setting)
- Restart services or reboot
In VS Server Explorer, I added a network connection (under Connections, not Servers) to myremotesqltesthost\SQLExpress instance. The Properties tab shows the connection string you can use in the next step.
In VS Developer command prompt (in project source main directory, e.g. “…..\src\myproject”), execute:
dotnet ef dbcontext scaffold "Data Source=mssqltest;Initial Catalog=myDB;Integrated Security=True" Microsoft.EntityFrameworkCore.SqlServer -o Models
(assuming the destination should be a subfolder named Models)
Note: This command syntax has been changing for about 3 – 5 times yet, at least what I noticed. It is not even up to date in the current MS tutorial. So I cannot guarantee this will still be working in a few months.
After these IMHO hardest beginning steps are done, you can proceed e.g. with editing project.json (or use a Nuget or Bower GUI) to add your project dependencies (other libraries, projects, …).
[:]