Show Menu
Cheatography

.Net Core 3.1 MVC w/MySQL Cheat Sheet by

Workflow for setting up a .Net 3.1 MVC Project with MySQL DB and EF

Step 1 - Terminal

dotnet tool install --global dotnet-ef
You will only ever need to run this once as it is a global install not project specific
dotnet new mvc --no-https -o Projec­tDi­rec­tor­yName
Creates new dotnet MVC Project for develo­pment

Step 2 - Terminal (Inside Project Directory)

dotnet add package Pomelo.En­tit­yFr­ame­wor­kCo­re.M­ySql --version 3.1.1
Entity Framework Core provider for MySQL DB's
dotnet add package Micros­oft.En­tit­yFr­ame­wor­kCo­re.D­esign --version 3.1.5
EF Core tools for DB creati­on/­mig­ration
These two commands must be run in your project folder every time you set up a new MVC project!

Step 3 - appset­tin­gs.json && Startup.cs

"­DBI­nfo­":
{
"­Nam­e": "­MyS­QLc­onn­ect­",
"­Con­nec­tio­nSt­rin­g": "­ser­ver­=lo­cal­hos­t;u­ser­id=­roo­t;p­ass­wor­d=r­oot­;po­rt=­330­6;d­ata­base=SchemaName;SslMode=None"
}
Add this at the end of your appset­tin­gs.json file.
Don't forget a comma before this new section!
using Micros­oft.En­tit­yFr­ame­wor­kCore;
Add this to your using statements at the top of Startup.cs
servic­es.A­dd­DbC­ont­ext­<My­Con­tex­t>(­options => option­s.U­seMySql (Confi­gur­ati­on[­"­DBI­nfo­:Co­nne­cti­onS­tri­ng"]));
Add this to the Config­ure­Ser­vices method in Startup.cs
servic­es.A­dd­Ses­sion();
Add this to the Config­ure­Ser­vices method in Startup.cs Adds Session
app.Us­eSe­ssi­on();
Add this to the Configure method in Startu­p.cs. Adds Session

Step 4 - Context File

using Microsoft.EntityFrameworkCore;
namespace ProjDirName.Models
{ 
    public class DBContextClassName : DbContext 
    { 
        public DBContextClassName(DbContextOptions options) : base(options) { }
        public DbSet<ModelName> TableName { get; set; }
        Add more tables here
    }
}
Sample code for context model file

Step 5 - Model Example

using System;
using System.ComponentModel.DataAnnotations;
namespace ProjDirName.Models
{
    public class ModelName
    {
        [Key]
        public int ModelNameId { get; set; }
        Set all properties here
        public DateTime CreatedAt { get; set; } = DateTime.Now;
        public DateTime UpdatedAt { get; set; } = DateTime.Now;
        Set Navigation Properties
    }
}
Class model for database table

Step 6 - Context Dependency Injection

using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using ProjDirName.Models;

public HomeController(ContextClassName context)
        {
            _context = context;
        }
     
        [HttpGet("")]
        public IActionResult Index()
        {
            List<ModelName> VariableName = _context.TableName.ToList();
            
            return View();
        }
Sample of the code required to make DB Context available in contro­llers

Step 7 - Terminal - Migration

dotnet ef migrations add Migrat­ionName
Creates a migration in prepar­ation for Creati­ng/­Upd­ating your DB Schema
dotnet ef database update
Updates Database schema with most recent migration
This step to be done after all previous steps are complete and, prefer­ably, after all models and relati­onships are in place.
       
 

Comments

Updated step 3 to include `using` statement required to set DBContext config

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.

          Related Cheat Sheets

          MySQL Cheat Sheet
          SQL Server Cheat Sheet
          Essential MySQL Cheat Sheet