- Note: .NET 7 version is in progress on branch
dotnet-7.0, pending resolution of issues. - Note: Microsoft SQL Server (MSSQL) now runs on Mac / Linux via Docker, and may be considered as a viable cross platform solution.
- Note: MySQL via Pomelo data adapter is functional, and a viable alternative to Oracle's adapters used here.
- Note: PostgreSQL alternative version is also available, with .NET 7 update complete.
Convert an ASP.NET Core Web Application project to use MySQL with Entity Framework, enabling development on macOS, linux, or Windows targets using IDEs such as VS Code, Visual Studio, or JetBrains Rider.
This project uses .NET 5.0 target framework, ASP.NET Core Web Application (Model-View-Controller) project scaffold from Visual Studio 2019 (version 16.10.1) to connect to MySQL 8.0.
For previous versions of .NET Core 3.x, 2.x, 1.x, see the releases for past implementations in this repository.
To immediately use this solution, make sure your environment setup is complete; then, jump to running the solution.
Make sure you have the .NET 5.0 SDK installed on your system.
If you're using Visual Studio Code, you will need to generate ASP.NET Core developer certificates by issuing the following commands from a terminal:
dotnet dev-certs https --clean
dotnet dev-certs https
For command line database ef commands, you will need to install Entity Framework Core tools .NET CLI:
dotnet tool install --global dotnet-ef
Make sure you have MySQL 8.0 Server installed on your system; or, use a Docker image instead of installing MySQL Server. In a terminal, execute the following to spin up a Docker image of MySQL:
docker run --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=mypassword -d mysql
Before the solution can be executed, Entity Framework migrations must be run to setup the database.
Configure connection string in project's appsettings.json, replacing the username, password, and database appropriately:
"ConnectionStrings": {
"DefaultConnection":"server=localhost;userid=myusername;password=mypassword;database=mydatabase;"
},Execute the migration using either Visual Studio Package Manager Console (from menu: Tools -> NuGet Package Manager -> Package Manager Console):
>> Update-Database
Or, from the command line via DotNet CLI, execute the following command inside the project directory, where the .csproj file is located:
$ dotnet ef database update
After running the migration, the database is created and web application is ready to be run.
Run the solution via your IDE; or, execute the following command line
dotnet run
Then, load via browser to either https or http endpoints:
Project setup has already been completed in this repository, ready for use as a template for your next project.
Otherwise, adapt the steps below to incorporate MySQL into your solution.
Install the MySql.EntityFrameworkCore NuGet package in the ASP.NET web application.
To do this, you can use the dotnet command line by executing:
dotnet add package MySql.EntityFrameworkCore --version 5.0.3.1
Or, edit the project's .csproj file and add the following line in the PackageReference item group:
<PackageReference Include="MySql.EntityFrameworkCore" Version="5.0.3.1" />
In Startup.cs under ConfigureServices() method, replace the UseSqlServer / UseSqlite option with MySQL:
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
// Add framework services.
services.AddDbContext<ApplicationDbContext>(options =>
options.UseMySQL(Configuration.GetConnectionString("DefaultConnection")));Upon upgrading MySQL Oracle Connector, Entity Framework migrations may fail with the errors:
MySql.Data.MySqlClient.MySqlException (0x80004005): Specified key was too long; max key length is 3072 bytes
MySql.Data.MySqlClient.MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'max) NULL, PRIMARY KEY (
Id))Failed executing DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE `AspNetRoles` ( `Id` TEXT NOT NULL, `Name` TEXT NULL, `NormalizedName` TEXT NULL, `ConcurrencyStamp` TEXT NULL, PRIMARY KEY (`Id`) );MySql.Data.MySqlClient.MySqlException (0x80004005): BLOB/TEXT column 'Id' used in key specification without a key length
CREATE TABLE `AspNetRoles` ( `Id` nvarchar(450) NOT NULL, `Name` nvarchar(256) NULL, `NormalizedName` nvarchar(256) NULL, `ConcurrencyStamp` nvarchar(max) NULL, PRIMARY KEY (`Id`) );MySql.Data.MySqlClient.MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'max) NULL,
To resolve this, add the following code within the ApplicationDbContext.cs OnModelCreating().
using Microsoft.AspNetCore.Identity;
using Microsoft.AspNetCore.Identity.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore;
public class ApplicationDbContext : IdentityDbContext
{
// ...
protected override void OnModelCreating(ModelBuilder builder)
{
base.OnModelCreating(builder);
builder.Entity<IdentityRole>(entity => entity.Property(m => m.Id).HasMaxLength(450));
builder.Entity<IdentityRole>(entity => entity.Property(m => m.ConcurrencyStamp).HasColumnType("varchar(256)"));
builder.Entity<IdentityUserLogin<string>>(entity =>
{
entity.Property(m => m.LoginProvider).HasMaxLength(127);
entity.Property(m => m.ProviderKey).HasMaxLength(127);
});
builder.Entity<IdentityUserRole<string>>(entity =>
{
entity.Property(m => m.UserId).HasMaxLength(127);
entity.Property(m => m.RoleId).HasMaxLength(127);
});
builder.Entity<IdentityUserToken<string>>(entity =>
{
entity.Property(m => m.UserId).HasMaxLength(127);
entity.Property(m => m.LoginProvider).HasMaxLength(127);
entity.Property(m => m.Name).HasMaxLength(127);
});
}Then, generate a new migration using Visual Studio Package Manager Console (from menu: Tools -> NuGet Package Manager -> Package Manager Console):
>> Add-Migration
Or, from the command line via DotNet CLI:
$ dotnet ef migrations add CreateIdentitySchema
If running dotnet ef fails initially, the __efmigrationshistory table may not exist. Past versions of Entity Framework migration tools failed to create this table.
Assure you're running the lastest tools:
dotnet tool update --global dotnet-ef
Otherwise, manually create the migrations history table in the MySQL database by executing the following SQL script.
use mydatabase;
CREATE TABLE `mydatabase`.`__EFMigrationsHistory` (
`MigrationId` text NOT NULL,
`ProductVersion` text NOT NULL,
PRIMARY KEY (`MigrationId`(255)));Note that MySql.Data.EntityFrameworkCore NuGet package is deprecated, and is now: MySql.EntityFrameworkCore.
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent