This article shows how to use MySQL with ASP.NET Core 3.1 using Entity Framework Core.

Code: https://github.com/damienbod/AspNetCoreMultipleProject

Posts in this series:

• Experiments with Entity Framework Core and ASP.NET Core MVC
• ASP.NET Core with PostgreSQL and Entity Framework Core
• ASP.NET Core with MySQL and Entity Framework Core

2020-01-10: Updated to ASP.NET Core 3.1
2018-12-05: Updated to ASP.NET Core 2.2 and EF Core 2.2
2017-06-16: Updated to ASP.NET Core 2.1
2017-08-17: Updated to ASP.NET Core 2.0 and Pomelo.EntityFrameworkCore.MySql
2017-02-10: Updated to VS2017 and msbuild
2016-12-01: Updated to ASP.NET Core 1.1

The Entity Framework MySQL package can be downloaded using the NuGet package Pomelo.EntityFrameworkCore.MySql. At present no official provider from MySQL exists for Entity Framework Core which can be used in an ASP.NET Core application.

The Pomelo.EntityFrameworkCore.MySql package can be added to the csproj file.

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <TargetFramework>netcoreapp3.1</TargetFramework>
    <AssemblyName>DataAccessMySqlProvider</AssemblyName>
    <PackageId>DataAccessMySqlProvider</PackageId>
  </PropertyGroup>

  <ItemGroup>
    <ProjectReference Include="..\DomainModel\DomainModel.csproj" />
  </ItemGroup>

  <ItemGroup>
    <FrameworkReference Include="Microsoft.AspNetCore.App" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="3.1.0" PrivateAssets="All" />
    <PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="3.1.0" />
  </ItemGroup>

</Project>

The web project which loads the project with EF Core needs to support migrations if you wish to create a database this way.

<Project Sdk="Microsoft.NET.Sdk.Web">

  <PropertyGroup>
    <TargetFramework>netcoreapp3.1</TargetFramework>
    <AssemblyName>AspNetCoreMultipleProject</AssemblyName>
    <PackageId>AspNet5MultipleProject</PackageId>
    <StartupObject>AspNetCoreMultipleProject.Program</StartupObject>
  </PropertyGroup>

  <ItemGroup>
    <ProjectReference Include="..\DataAccessMsSqlServerProvider\DataAccessMsSqlServerProvider.csproj" />
    <ProjectReference Include="..\DataAccessMySqlProvider\DataAccessMySqlProvider.csproj" />
    <ProjectReference Include="..\DataAccessPostgreSqlProvider\DataAccessPostgreSqlProvider.csproj" />
    <ProjectReference Include="..\DataAccessSqliteProvider\DataAccessSqliteProvider.csproj" />
    <ProjectReference Include="..\DomainModel\DomainModel.csproj" />
  </ItemGroup>
  
  <ItemGroup>
    <PackageReference Include="Microsoft.AspNetCore.App" />
    <PackageReference Include="Microsoft.AspNetCore.Mvc.NewtonsoftJson" Version="3.1.0" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="3.1.0" PrivateAssets="All" />
    <PackageReference Include="Newtonsoft.Json" Version="12.0.3" />
    <PackageReference Include="Swashbuckle.AspNetCore" Version="5.0.0-rc5" />
  </ItemGroup>
  <ItemGroup>
    <DotNetCliToolReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Tools" Version="2.0.0" />
  </ItemGroup>
</Project>

An EfCore DbContext can be added like any other context supported by Entity Framework Core.

using System;
using System.Linq;
using DomainModel.Model;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;

namespace DataAccessMySqlProvider
{ 
    // >dotnet ef migration add testMigration
    public class DomainModelMySqlContext : DbContext
    {
        public DomainModelMySqlContext(DbContextOptions<DomainModelMySqlContext> options) :base(options)
        { }
        
        public DbSet<DataEventRecord> DataEventRecords { get; set; }

        public DbSet<SourceInfo> SourceInfos { get; set; }

        protected override void OnModelCreating(ModelBuilder builder)
        {
            builder.Entity<DataEventRecord>().HasKey(m => m.DataEventRecordId);
            builder.Entity<SourceInfo>().HasKey(m => m.SourceInfoId);

            // shadow properties
            builder.Entity<DataEventRecord>().Property<DateTime>("UpdatedTimestamp");
            builder.Entity<SourceInfo>().Property<DateTime>("UpdatedTimestamp");

            base.OnModelCreating(builder);
        }

        public override int SaveChanges()
        {
            ChangeTracker.DetectChanges();

            updateUpdatedProperty<SourceInfo>();
            updateUpdatedProperty<DataEventRecord>();

            return base.SaveChanges();
        }

        private void updateUpdatedProperty<T>() where T : class
        {
            var modifiedSourceInfo =
                ChangeTracker.Entries<T>()
                    .Where(e => e.State == EntityState.Added || e.State == EntityState.Modified);

            foreach (var entry in modifiedSourceInfo)
            {
                entry.Property("UpdatedTimestamp").CurrentValue = DateTime.UtcNow;
            }
        }
    }
}

In an ASP.NET Core web application, the DbContext is added to the application in the startup class. In this example, the DbContext is defined in a different class library. The MigrationsAssembly needs to be defined, so that the migrations will work. If the context and the migrations are defined in the same assembly, this is not required.

public Startup(IHostingEnvironment env)
{
	var builder = new ConfigurationBuilder()
		.SetBasePath(env.ContentRootPath)
		.AddJsonFile("appsettings.json", optional: true, reloadOnChange: true)
		.AddJsonFile("config.json", optional: true, reloadOnChange: true);

	Configuration = builder.Build();
}
		
public void ConfigureServices(IServiceCollection services)
{	
	var sqlConnectionString = Configuration.GetConnectionString("DataAccessMySqlProvider");

	services.AddDbContext<DomainModelMySqlContext>(options =>
		options.UseMySQL(
			sqlConnectionString,
			b => b.MigrationsAssembly("AspNetCoreMultipleProject")
		)
	);
}

The application uses the configuration from the config.json. This file is used to get the MySQL connection string, which is used in the Startup class.

{
    "ConnectionStrings": {  
        "DataAccessMySqlProvider": "server=localhost;userid=damienbod;password=1234;database=damienbod;"
        }
    }
}

MySQL workbench can be used to add the schema ‘damienbod’ to the MySQL database. The user ‘damienbod’ is also required, which must match the defined user in the connection string. If you configure the MySQL database differently, then you need to change the connection string in the config.json file.

Now the database migrations can be created and the database can be updated.

>
> dotnet ef migrations add mySqlMigration --context DomainModelMySqlContext
>
> dotnet ef database update --context DomainModelMySqlContext
>

If successful, the tables are created.

The MySQL provider can be used in a MVC controller using construction injection.

using System.Collections.Generic;
using DomainModel;
using DomainModel.Model;
using Microsoft.AspNetCore.Mvc;
using Newtonsoft.Json;

namespace AspNet5MultipleProject.Controllers
{
    [Route("api/[controller]")]
    public class DataEventRecordsController : Controller
    {
        private readonly IDataAccessProvider _dataAccessProvider;

        public DataEventRecordsController(IDataAccessProvider dataAccessProvider)
        {
            _dataAccessProvider = dataAccessProvider;
        }

        [HttpGet]
        public IEnumerable<DataEventRecord> Get()
        {
            return _dataAccessProvider.GetDataEventRecords();
        }

        [HttpGet]
        [Route("SourceInfos")]
        public IEnumerable<SourceInfo> GetSourceInfos(bool withChildren)
        {
            return _dataAccessProvider.GetSourceInfos(withChildren);
        }

        [HttpGet("{id}")]
        public DataEventRecord Get(long id)
        {
            return _dataAccessProvider.GetDataEventRecord(id);
        }

        [HttpPost]
        public void Post([FromBody]DataEventRecord value)
        {
            _dataAccessProvider.AddDataEventRecord(value);
        }

        [HttpPut("{id}")]
        public void Put(long id, [FromBody]DataEventRecord value)
        {
            _dataAccessProvider.UpdateDataEventRecord(id, value);
        }

        [HttpDelete("{id}")]
        public void Delete(long id)
        {
            _dataAccessProvider.DeleteDataEventRecord(id);
        }
    }
}

The controller api can be called using Fiddler:

POST http://localhost:5000/api/dataeventrecords HTTP/1.1
User-Agent: Fiddler
Host: localhost:5000
Content-Length: 135
Content-Type: application/json;
 
{
  "DataEventRecordId":3,
  "Name":"Funny data",
  "Description":"yes",
  "Timestamp":"2015-12-27T08:31:35Z",
   "SourceInfo":
  { 
    "SourceInfoId":0,
    "Name":"Beauty",
    "Description":"second Source",
    "Timestamp":"2015-12-23T08:31:35+01:00",
    "DataEventRecords":[]
  },
 "SourceInfoId":0 
}

The data is added to the database as required.

Links:

https://docs.microsoft.com/en-us/ef/core/

https://github.com/SapientGuardian/SapientGuardian.EntityFrameworkCore.MySql

http://dev.mysql.com/downloads/mysql/

Experiments with Entity Framework Core and an ASP.NET Core API

https://docs.efproject.net/en/latest/miscellaneous/connection-strings.html

http://insidemysql.com/howto-starting-with-mysql-ef-core-provider-and-connectornet-7-0-4/