Repository files navigation

• Note: .NET 7 version is in progress on branchdotnet-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: PostgreSQLalternative version is also available, with .NET 7 update complete.

Convert anASP.NET Core Web Application project to useMySQL withEntity Framework, enabling development on macOS, linux, or Windows targets using IDEs such asVS Code,Visual Studio, orJetBrains 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 thereleases for past implementations in this repository.

To immediately use this solution, make sure yourenvironment setup is complete; then, jump torunning 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 --cleandotnet dev-certs https

For command linedatabase ef commands, you will need to install Entity Framework Core tools .NET CLI:

dotnet tool install --global dotnet-ef

Make sure you haveMySQL 8.0 Server installed on your system; or, use aDocker 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 theusername,password, anddatabase 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:

• https://localhost:5001
• http://localhost:5000

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 theMySql.EntityFrameworkCore NuGet package in the ASP.NET web application.

To do this, you can use thedotnet 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 thePackageReference item group:

<PackageReference Include="MySql.EntityFrameworkCore" Version="5.0.3.1" />

InStartup.cs underConfigureServices() method, replace theUseSqlServer /UseSqlite option with MySQL:

// This method gets called by the runtime. Use this method to add services to the container.publicvoidConfigureServices(IServiceCollectionservices){// 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']

CREATETABLE `AspNetRoles` (`Id`TEXTNOT NULL,`Name`TEXTNULL,`NormalizedName`TEXTNULL,`ConcurrencyStamp`TEXTNULL,PRIMARY KEY (`Id`));

MySql.Data.MySqlClient.MySqlException (0x80004005): BLOB/TEXT column 'Id' used in key specification without a key length

CREATETABLE `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.csOnModelCreating().

usingMicrosoft.AspNetCore.Identity;usingMicrosoft.AspNetCore.Identity.EntityFrameworkCore;usingMicrosoft.EntityFrameworkCore;publicclassApplicationDbContext:IdentityDbContext{// ...protectedoverridevoidOnModelCreating(ModelBuilderbuilder){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 runningdotnet 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;CREATETABLE `mydatabase`.`__EFMigrationsHistory` (`MigrationId`textNOT NULL,`ProductVersion`textNOT NULL,PRIMARY KEY (`MigrationId`(255)));

Note thatMySql.Data.EntityFrameworkCore NuGet package is deprecated, and is now:MySql.EntityFrameworkCore.