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 reverese 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, there 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 wqell 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.
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:
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, IEnumerable`1 tables, IEnumerable`1 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
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.
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 labeled within the generated
DbContext, specifically regarding removing the hard coded connection string.
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
#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.
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.
A unique background as business owner, marketing, software development and business development ensures that he can offer the optimum business consultancy services across a wide spectrum of business challenges.