In this article I’ll show step by step instructions of correct use Entity Framework Core in your ASP.NET Core project with MySql Database and traditional EF migrations. All actions was made in Visual Studio 2019 on Windows 10 machine with installed MySql server. In example used ASP.NET Core
Wep API project (.NET Core v3.1). First of all you have to know some basic questions.I discussed them below. ASP.NET Core is a cross-platform, high-performance, open-source framework for building modern cloud-based, Internet-connected applications. With ASP.NET Core, you can: Build web apps and services, IoT apps, and mobile backends. Use your favorite development tools on Windows, macOS, and Linux. A Web API is an application programming interface for either a web server or a web browser. It is a web development concept, usually limited to a web application's client-side (including any web frameworks being used), and thus usually does not include web server or browser implementation details such as SAPIs or APIs unless publicly accessible by a remote web application Entity Framework is an Object
Relational Mapper (ORM) which is a type of tool that simplifies mapping between objects in your software to the tables and columns of a relational database. Object-relational mapping in computer science is a programming technique for converting data between incompatible type systems using object-oriented programming languages. This creates, in effect, a "virtual object database" that can be used from within the programming language. MySQL is an open source relational database.MySQL is cross platform which means it runs on a number of different platforms such as Windows, Linux, and Mac OS etc. This guide explains setting up a production-ready ASP.NET Core Web API using Entity Framework with MySQL Database. Our Web API can perform basic CRUD operations. Following the steps to install MySQL in your windows 10 The general MySQL Installer download is available at this link.MySQL Workbench can be installed using the Windows MSI Installer package. The MSI package bears the name mysql-installer-web-community-version.msi, where version indicates the MySQL Workbench version number, and arch the build architecture (winx64). The MySQL Installer application can install, upgrade, and manage most MySQL products, including MySQL Workbench. Next-->How to Create an ASP.NET Core Web API with Entity Framework using MySQL
Introduction
What is ASP.NET Core?
What
is Web API?
What is Entity Framework?
What is ORM ?
What is MySQL?
EF Core in ASP.NET Core Web API with MySql Database
In this article, you will
learn how to
Required Tools
Step 1: MySQL Installation
Download
MySQL
Setup My SQL including MySQL Workbench
You are set...
Step 2: Create a Database using MySQL Workbench
You can use the MySQL Workbench GUI to create a database. You can also create a database programmatically but here's how to do it via the GUI.
In the following example, we create a new database called "StudentDB".
- Click your connection,and log in your account with password.
Click the icon for creating a new schema (you'll find this on the Workbench toolbar):
Enter the schema name (in this case, StudentDB) and the default collation, then click the Apply button
- You are prompted to review the SQL statement that will be run to create the database. To run the statement (and create the database) click Apply:
- You should see the following screen once the database has been created:
- The database has now been created. You will now see your new database listed under the SCHEMAS tab on the left pane:
Step 3: Create a ASP.NET Core Web API
- From the File menu, select New > Project.
- Select the ASP.NET Core Web Application template and click Next.
- Name the project CRUDWebAPIMySQL and click Create.
- In the Create a new ASP.NET Core Web Application dialog, confirm that .NET Core and ASP.NET Core 3.1 are selected. Select the API template and click Create.
N.B. Unselect the configure for Https Press Ctrl+F5 to run the app.
Step 4: Adding Dependencies in ASP.NET Core
Before we start our project need a few dependencies. We will add them all by NuGet Package Manager.
The list of packages is below:
- Microsoft.EntityFrameworkCore
- Pomelo.EntityFrameworkCore.MySql
- Microsoft.EntityFrameworkCore.Tools
- Microsoft.EntityFrameworkCore.Design
I use Polemo provider instead Oracle provider (MySql.Data.EntityFrameworkCore) because Oracle’s connector doesn’t support EF migrations, also relationships there can be implemented only in Fluent.API. EF Tools I installed just for simplifying entering commands in Package Manager Console (Final Step of this story).
Step 5: Adding Models and Database Context
For start we need basic EF Models and DbContext. In this example I will create simple models with user and his pets. This example also will demonstrate auto-creation of relationships by Entity Framework. Firstly create a folder named Models,then create
StudentDetail.cs Model Class
using System.ComponentModel.DataAnnotations; using System.ComponentModel.DataAnnotations.Schema; namespace CRUDWebAPIWithMySQL.Models { public class StudentDetail { [Key] [DatabaseGenerated(DatabaseGeneratedOption.Identity)] public int ID { get; set; } [Required] [Column(TypeName = "nvarchar(100)")] public string Name { get; set; } [Required] [Column(TypeName = "varchar(11)")] public string Phone_Number { get; set; } [Required] [Column(TypeName = "varchar(10)")] public string Birth_Date { get; set; } } }
Then Create DBContext file named StudentDetailContext.cs
using Microsoft.EntityFrameworkCore; using System; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; namespace CRUDWebAPIWithMySQL.Models { public class StudentDetailContext : DbContext { public StudentDetailContext(DbContextOptions<StudentDetailContext> options) : base(options) { } public DbSet<StudentDetail> StudentDetails { get; set; } //Table Name StudentDetails } }
Step 6 : Configure Dependency Injection
In this example I will save our connection string in appsettins.json file, because it useful in work with Git and Security measures.
"ConnectionStrings": { "DBConn": "Server=<Your IP>;port=3306;Database=studentdb;User Id=root;Password=1234;" }
Here is our's. We get our mySQL Server IP and port number from MySQL Connection
"ConnectionStrings": { "DBConn": "Server=localhost;port=3306;Database=studentdb;User Id=root;Password=1234;" }
Now we must notify about existing our database context in ConfigureServices method of Startup.cs . NOTICE that in configuration you must use AddDbContextPool method instead AddDbContext. You must import this three. Entity Framework, Pomelo EF and your Model and DBContext class which are in Models folder.
using Pomelo.EntityFrameworkCore.MySql.Infrastructure; using CRUDWebAPIWithMySQL.Models; using Microsoft.EntityFrameworkCore;
Then adding AddDbContextPool method for connection.
public void ConfigureServices(IServiceCollection services) { services.AddDbContextPool<Your DBContext Class name>( options => options.UseMySql(Configuration.GetConnectionString(<Connection String Name>) )); services.AddMvc(); }
public void ConfigureServices(IServiceCollection services) { services.AddDbContextPool<StudentDetailContext>( options => options.UseMySql(Configuration.GetConnectionString("DBConn") )); services.AddControllers(); }
Then create Controller Class. VS create it automatically. Following the steps below.
- Click right button of mouse and add controller class
- Select API Controller with action using EF
- Select your Model class and our DB Context Class
Step 7 : Migrations
If you installed EF Tools as me, than you can just run next 2 commands in Package Manager Console:
open your Package Manager Console
Add-Migration <MigrationName> Update-Database
Here is mine,
Add-Migration InitialCreate Update-Database
If any problem in your Migration delete your previous migrations.
After a successful migration , a table named StudentDetails in studentdb database
Step 8 : Final Checking
We are now all set. I added some rows myself. Open MySQL Workbench ,then select your table then adding one row.
- Select 1st 100 rows
- Then Edit first Row ,not needed to edit ID because it's identity type(auto incremented)
- You are prompted to review the SQL statement that will be run to create the database. To run the statement (and create the database) click Apply:
Now go to controller class named StudentDetailsController.cs for getting your APIs link.
Here Our sub domain is api/StudentDetails
Now
- build your project and run.
Here is an example.This API works for getting details of all student,
//localhost:2029/api/StudentDetails
Or GET request through Postman
For adding row ,we can POST request through Postman