How to reverse engineer MySql Database EF Core

There are many instances in software development projects when there will be a requirement to reverse engineer an existing database, in order to continue with a development project.EF Core has made the task of reverse engineering existing databases really easy. Although I had some issues with MySql which I thought I would help clarify just in case there were others with the same issues.

In this post, I will provide details on how to reverse engineer a MySql database using EF Core.

I have recently engaged on a new project for a client, who had a simple Candidate registration application, developed using PHP and the Laravel Framework with a MySql database backend. The client wanted to port the application to C# and .net Core. However, their intention is first to develop a .net core Web API and to deploy this to Amazon AWS.

I won't go into the reasons why the client wanted to do this type of technological and application refresh, as that is beyond the scope of this article. Suffice to say either technology is well suited for this task, however the client has there reasons and a business case.

Reverse Engineering a MySql Database

Microsoft Entity Framework core has made it really easy to Reverse Engineer existing databases, which are now included optional libraries available as nuget packages.

In previous versions of Entity Framework, reverse engineering was available via the Entity Framework Power Tools, and as far as I can recall, I don't think there was on option to do any other databases except SQL. ALthough I have to be honest, during those years I was predominantly focused on MS SQL, so this statement my not be 100%.

Although the process of Reverse Engineering MySQL databases, is ultimately a trivial process, I did have some issues. I have to preface this with, it could quite possibly be my Development Environment, could be an anomoly compared to others.

I specifically use Jetbrains Rider 2018.2 on Ubuntu 18.04 with .net Core 2.1 installed.

How to reverse MySQL database

I created a .net core Class Library Project, making use of the default template with DotnetCore CLI -

dotnet new classlib -n Candidate.Database  -o Candidate.Database

The usual process would be add the Nuget Package References:

Microsoft.EntityFrameworkCore.Tools
MySql.Data.EntityFrameworkCore.Design
MySql.Data.EntityFrameworkCore

Then simply use the terminal command in your Project Directory

dotnet ef dbcontext scaffold server=localhost;uid=yourUsername;pwd=yourpassword;database=candidate" MySql.Data.EntityFrameworkCore --schema candidate

The issue for me was that this didn't work and I received the following error

System.NotImplementedException: The method or operation is not implemented. at MySql.Data.EntityFrameworkCore.Scaffolding.Internal.MySQLDatabaseModelFactory.Create(String connectionString, IEnumerable1 tables, IEnumerable1 schemas)

It seems that the Official MySql nuget package doesn't have the necessary method implemented.

Fortunately, conducting a search of avaiable nuget packages I was able to find suitable packages, I found the Pomelo Foundatiion - Pomelo.EntityFrameworkCore.MySql which worked.

All I needed to do was import the packages

Microsoft.EntityFrameworkCore.Tools
Pomelo.EntityFrameworkCore.MySql.Design
Pomelo.EntityFrameworkCore.MySql

I was then able to use the Terminal Command:

dotnet ef dbcontext scaffold "server=localhost;uid=yourusername;pwd=yourpassword;database=candidate" Pomelo.EntityFrameworkCore.MySql --schema candidate

My Database Context and Entities were successfully generated.

Conclusion

This is a great start and drastically reduces the effort.

Although the generated code can be used as it - subject to you addressing some the warnings which a clearly labelled within the generated, DbContext, specifically regarding removing the hard-coded connection string.

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
   if (!optionsBuilder.IsConfigured)
   {
     /* warning To protect potentially sensitive information in your 
     connection string, you should move it out of source code. See 
     http://go.microsoft.com/fwlink/?LinkId=723263 for guidance on 
        storing connection strings.
     */
                
 
 optionsBuilder.UseMySql("server=localhost;uid=yourusername;pwd=yourp 
assword;database=candidate");
    }
 }

I found my OCD kicking in and I had to do some tidying up and re-organising the generated code, but for the most part, the generated code works and simplifies your process.

About
Latest Posts

Gary Woodfine

Technical Director at threenine.co.uk

Gary is Technical Director at threenine.co.uk, an independent software vendor specialising in IoT, Field Service and associated managed services,enabling customers to be efficient, productive, secure and scale-able.