This guide demonstrates how to implement data access in an ASP.NET Core Web API using Entity Framework Core with MySQL. The examples assume a Database First approach where the database schema already exists.

Required NuGet Packages

Install the following packages via NuGet Package Manager or CLI:

• Microsoft.EntityFrameworkCore
• Pomelo.EntityFrameworkCore.MySql

Connection String Configuration

Add the MySQL connection string to appsettings.json:

{
  "ConnectionStrings": {
    "DefaultConnection": "server=localhost;userid=root;pwd=root;database=TestDb;charset=utf8mb4;pooling=false"
  }
}

DbContext Implementation

Create a DbContext class that derives from DbContext and configures the MySQL connection:

public class ApiDataContext : DbContext
{
    private readonly IConfiguration _configuration;

    public ApiDataContext(DbContextOptions<ApiDataContext> options, IConfiguration configuration)
        : base(options)
    {
        _configuration = configuration;
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        base.OnConfiguring(optionsBuilder);
        
        var connectionString = _configuration.GetConnectionString("DefaultConnection");
        optionsBuilder.UseMySql(connectionString, ServerVersion.AutoDetect(connectionString));
    }

    public DbSet<TemperatureRecord> TemperatureRecord { get; set; }
}

Service Registration

Register the DbContext and repository in the ConfigureServices method of the Startup class:

public void ConfigureServices(IServiceCollection services)
{
    services.AddControllers();
    
    services.AddDbContext<ApiDataContext>();
    
    services.AddScoped<ITemperatureRecordRepository, TemperatureRecordRepository>();
}

Repository Pattern Implementation

Define an interface and concrete implementation for the repository:

public interface ITemperatureRecordRepository
{
    IQueryable<TemperatureRecord> GetAllRecords();
}

public class TemperatureRecordRepository : ITemperatureRecordRepository
{
    private readonly ApiDataContext _dataContext;

    public TemperatureRecordRepository(ApiDataContext dataContext)
    {
        _dataContext = dataContext;
    }

    public IQueryable<TemperatureRecord> GetAllRecords()
    {
        return _dataContext.TemperatureRecord;
    }
}

Controller Implementation

Inject the repository into the controller through constructor injection:

[ApiController]
[Route("api/[controller]")]
public class TemperatureController : ControllerBase
{
    private readonly ITemperatureRecordRepository _repository;

    public TemperatureController(ITemperatureRecordRepository repository)
    {
        _repository = repository;
    }

    [HttpGet]
    public IQueryable<TemperatureRecord> Get()
    {
        return _repository.GetAllRecords();
    }
}

Important Considerations

Database Prerequisites: The Database First approach requires that the target database and tables already exist. Create these manually before running the application.

Package Version Requirements: When using Pomelo.EntityFrameworkCore.MySql with ASP.NET Core 3.1, install version 5.0.0-alpha.2 or later (pre-release). Version 3.2.4 may cause runtime exceptions:

System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.Bcl.AsyncInterfaces, Version=1.0.0.0, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51'

Table Naming Convention: The DbSet property name must match the actual table name in the database exactly. Using pluralized names like TemperatureRecords when the table is named TemperatureRecord results in:

MySqlException: Table 'testdb.temperaturerecords' doesn't exist

This mapping can be customized using attributes or fluent API configuration if the database schema uses different naming conventions.