.Net Core

Scaffold MySQL Database using Dotnet Core | Database First Approach

Google+ Pinterest LinkedIn Tumblr

Scaffolding means creating Models from an Existing Database using Entity Framework Core. Like .Net Framework, EF Core also supports Scaffolding an Existing MySQL Database.

In .Net Core early release, creating Models from a MySQL DB was quite difficult because of no fully supported MySQL library. Thanks to Pomelo for providing developers fully supported solution for MySQL at that time but In this Tutorial, we’re not going to use Pomelo.

Now, Scaffolding your Existing Database is really simple.

Let’s start by creating a simple Database.

We have a very simple Database of an Organization. We’ll scaffold this Database in our Dotnet Core Project.

Here’s the SQL for for the above DB.

-- phpMyAdmin SQL Dump
-- version 4.8.5
-- https://www.phpmyadmin.net/
--
-- Host: localhost
-- Generation Time: Jun 16, 2019 at 07:24 PM
-- Server version: 10.1.39-MariaDB
-- PHP Version: 7.3.5

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

--
-- Database: `Organization`
--

-- --------------------------------------------------------

--
-- Table structure for table `Departments`
--

CREATE TABLE `Departments` (
  `Id` int(11) NOT NULL,
  `Name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `Employees`
--

CREATE TABLE `Employees` (
  `Id` int(11) NOT NULL,
  `Name` varchar(255) NOT NULL,
  `Email` varchar(55) NOT NULL,
  `Password` varchar(255) NOT NULL,
  `Contact` varchar(15) NOT NULL,
  `Address` varchar(255) NOT NULL,
  `DepartmentId` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `Departments`
--
ALTER TABLE `Departments`
  ADD PRIMARY KEY (`Id`);

--
-- Indexes for table `Employees`
--
ALTER TABLE `Employees`
  ADD PRIMARY KEY (`Id`),
  ADD KEY `DepartmentId` (`DepartmentId`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `Departments`
--
ALTER TABLE `Departments`
  MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `Employees`
--
ALTER TABLE `Employees`
  MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `Employees`
--
ALTER TABLE `Employees`
  ADD CONSTRAINT `Employees_ibfk_1` FOREIGN KEY (`DepartmentId`) REFERENCES `Departments` (`Id`);
COMMIT;

/*!40101 SET [email protected]_CHARACTER_SET_CLIENT */;
/*!40101 SET [email protected]_CHARACTER_SET_RESULTS */;
/*!40101 SET [email protected]_COLLATION_CONNECTION */;

After creating Database, we need to Create a new .Net Core Project for Scaffolding our Database.

Let’s create a new empty .Net Core project

I’m going to use dotnet cli for creating new project using the command below.

dotnet new empty

Create a new Folder as Models at the root of your Project.

Now, we need to install some NuGet packages necessary to Scaffold our existing MySQL DB.

First of all, install EF Core package from NuGet using dotnet cli.

dotnet add package MySql.Data.EntityFrameworkCore --version 8.0.13

We also need EF Core Design Package which can also be installed NuGet Package Manager.

So, use the command below to install  Microsoft.EntityFrameworkCore.Design Nuget package.

dotnet add package Microsoft.EntityFrameworkCore.Design

Now, It’s time to create our Models from Database.

Scaffolding Database

Run the below command

dotnet ef dbcontext scaffold "server=localhost;port=3306;user=root;password=12345;database=Organization" MySql.Data.EntityFrameworkCore -o Models

Please update your Credentials (Database name, username & password) before running the command.

Your Models will be created after the above command inside your Models Folder something like this.

Updating Models

You can also update your Models using a simple modification in the above command.

Simply add -f after the above scaffolding command. It will look like this.

dotnet ef dbcontext scaffold "server=localhost;port=3306;user=root;password=12345;database=Organization" MySql.Data.EntityFrameworkCore -o Models -f

I hope everything went fine. Please comment If you got any difficulty while following this article. I’ll love to solve your problem.

Here’re some more articles, you might be interested:

– TOP OPEN SOURCE ASP.NET CORE CONTENT MANAGEMENT SYSTEM (CMS)

– USING NOSQL DATABASE WITH DOTNET CORE EXAMPLE

– GENERATE QR CODE USING ASP.NET CORE

Author

I'm passionate about learning new technologies as well as mentoring and helping others get started with their programming career. This blog is my way of giving back to the Community.

Write A Comment