HomeFeaturedAboutContact
.NET
Scaffold MySQL Database using Dotnet Core | Database First Approach
Shehryar Khan
Shehryar Khan
January 16, 2022
1 min

Table Of Contents

01
Introduction
02
Creating Database
03
Setting Up Project
04
Scaffolding Database

Introduction

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.

Creating Database

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 CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

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

Setting Up Project

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
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.

Related Articles:

Generate QR Code using ASP.NET

How to Create Soap Web Services in DotNet Core


Tags

#ScaffoldMySQL#DBFirst
Shehryar Khan

Shehryar Khan

Full-Stack .NET Developer

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.

Expertise

.NET
ASP.NET
React

Social Media

instagramtwitterwebsite

Related Posts

dropthedot
Top 15 Memes of Twitter DropTheDot Campaign Hashtag
August 19, 2022
1 min
© 2023, All Rights Reserved.

Quick Links

Advertise with usContact Us

Social Media