Configuring ASP.Net Core 2.0 to connect to MySQL

I need to use MySQL for my next project and have searched around to configure my ASP.Net Core API application to connect MySQL, using code first.

Tools

I use the following on Microsoft Windows 10

  1. Visual Studio 2017
  2. MySQL Server
  3. HeidiSQL to run some queries

Steps

Followings are the steps I took to accomplish this.

  • Create the model for my table. For example
public class Todo
{
public Guid Id { get; set; }
public string Title { get; set; }
public Boolean IsDone { get; set; }
}
  • I want to use Guid as a key of the table. Since MySQL does not support Guid, I need to tell EF how the key column should be configured as follows
[Key]
[Column(TypeName = “char(36)”)]
public string Id {get; set; }
  • Create the Db context class
public class TodoContext : DbContext
{
public TodoContext(DbContextOptions<TodoContext> options) : base(options)
{
}

public DbSet<Todo> Todo {get; set;}
}
  • Add the MySql.Data.EntityFrameworkCore package using Package Manager
  • Add the MySQL connection string in the appsettings.json file.
“ConnectionStrings”: {
“MySQLConnectionString”: “server=localhost;database=todos;user=user;password=password; Sslmode=none”
}
  • Create the database todos in MySQL.
  • Create the corresponding user. In my case, user with password password.
  • Using package manager console, add migration.
PM> add-migration initialdb
  • Update database
PM> update-database;

  • You might get the following error
Failed executing DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT `MigrationId`, `ProductVersion`
FROM `__EFMigrationsHistory`
ORDER BY `MigrationId`;
  • This means you need to create the migration history table manually. Open up HeidiSQL and run the following query
CREATE TABLE `__EFMigrationsHistory` ( `MigrationId` nvarchar(150) NOT NULL, `ProductVersion` nvarchar(32) NOT NULL, PRIMARY KEY (`MigrationId`) );
  • Update database again. Should be successful.
  • See your tables created in MySQL using HeidiSQL.

References