MySQL with Entity Framework Core

I needed a way for a new API NET CORE Web API project to surface data from an existing MySQL instance, EF Core felt like a simple solution. These were the steps I followed. The API was built ontop of net8.0 for reasons of LTS and its what the cool kids are using 😎

  1. Install nuget packages, for compatibility reasons with the OData packages I needed to use these versions but latest will probably work for you.
1
2
3
4
EFCore.NamingConventions               v8.0.0
Microsoft.EntityFrameworkCore          v8.0.13
Microsoft.EntityFrameworkCore.Design   v8.0.13
Pomelo.EntityFrameworkCore.MySql       v8.0.3
  1. Create the domain models adding the relationships, here one Risk had many RiskLines. Additionally add foreign keys and navigation props between the objects.
1
2
3
4
5
6
7
8
9
10
namespace Foo.Api.Domain.Models;

public class Risk
{
    public int Id { get; set; }
    public DateTime InsertDate { get; set; }
    ...

    public List<RiskLine> RiskLines { get; set; } = [];
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
namespace Foo.Api.Domain.Models;

public class RiskLine
{
    public int RiskId { get; set; } // Foreign key to the Risk table
    public Risk Risk { get; set; } = null!; // Navigation property to the parent Risk

    public int Id { get; set; }
    public DateTime InsertDate { get; set; }
    ...

    /// <summary>
    /// This is `risk` in the database
    /// </summary>
    public string RiskValue { get; set; }
}

  1. Define the database context and pass the options the base object. Configure the table and column mapping, case fixes, name clashes and relationships.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
using Microsoft.EntityFrameworkCore;
using Foo.Api.Domain.Models;

namespace Foo.Api.Infrastructure;

public class DatabaseContext(DbContextOptions options) : DbContext(options)
{
    public DbSet<Risk> Risk { get; set; }
    public DbSet<RiskLine> RiskLine { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Map tables
        modelBuilder.Entity<Risk>().ToTable("risk");
        modelBuilder.Entity<RiskLine>().ToTable("risk_line");

        // Map columns where needed
        modelBuilder.Entity<Risk>(entity =>
        {
            // Not all the columns can be mapped with `UseSnakeCaseNamingConvention` in Program.cs
            entity.Property(e => e.RiskIdReevaluation).HasColumnName("risk_id_re_evaluation");
        });

        modelBuilder.Entity<RiskLine>(entity =>
        {
            // Fix name clash
            entity.Property(e => e.RiskValue).HasColumnName("risk");
        });

        // Configure the one-to-many relationships
        modelBuilder.Entity<Risk>(entity =>
        {
            entity.HasMany(r => r.RiskLines)
                  .WithOne(rl => rl.Risk) // It has one corresponding Risk entity
                  .HasForeignKey(rl => rl.RiskId) // The foreign key is the `risk_id` column on the risk_line table
                  .IsRequired(false); // Optional: if you don't want EF Core to track the parent-child relationship
                                      // from the RiskLine side.
        });
    }
}
  1. Update appsettings.json to have the connection string, I just love how easy it is to read from appsettings in .Net 💋
1
2
3
4
"ConnectionStrings": 
{
    "FooConnectionString": "Server=foo.mysql.database.azure.com;Database=mydb;Uid=myuser;Pwd=mypass;"
}
  1. Inject the context into the DI container and configure the options.
1
2
3
4
5
6
7
var connectionString = builder.Configuration.GetConnectionString("FooConnectionString");
builder.Services.AddDbContext<DatabaseContext>(options =>
{
    options.UseMySql(connectionString, ServerVersion.AutoDetect(connectionString));
    options.UseSnakeCaseNamingConvention(); // attempt to map mysql tables (eg: myshema.risk.insert_date)
                                            // to domain entities (eg: Risk.InsertDate)
});
  1. Create the public DTO objects, this often feels like duplication but this ensures you dont leak domain concearns into your public API.

Here you may need to configure values to be nullable.

1
2
3
4
5
6
7
8
9
10
namespace Foo.Api.Models;

public class RiskDto
{
    public int Id { get; set; }
    public DateTime InsertDate { get; set; }
    ...

    public List<RiskLineDto> RiskLines { get; set; }
}
1
2
3
4
5
6
7
8
9
10
11
namespace Foo.Api.Models;

public class RiskLineDto
{
    public int Id { get; set; }
    public int RiskId { get; set; }
    public DateTime InsertDate { get; set; }
    ...
    public string? FooValue { get; set; } // nullable
}

  1. Create mappers for Domain to Dto objects:
1
2
3
4
5
6
7
8
9
10
11
using Foo.Api.Domain.Models;
using Foo.Api.Models;

namespace Foo.Api.Mappers
{
    public interface IRiskMapper
    {
        RiskDto ToDto(Risk risk);
        List<RiskDto> ToDtoList(IEnumerable<Risk> risks);
    }
}
  1. Finally inject the context and return the values, here the ProblemDetails will expose internal details, this was just for testing. In production you would log the exception and raise a ProblemDetails object sensible for your consumers. These endpoints are also a bit wierd because they used with OData annotations not shown here.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
public async Task<ActionResult<IEnumerable<RiskDto>>> Get()
{
    try
    {
        var items = await databaseContext.Risk
            .Include(r => r.RiskLines)
            .ToListAsync();

        var dto = riskMapper.ToDtoList(items);

        return Ok(dto);
    }
    catch (Exception ex)
    {
        return StatusCode(500, new ProblemDetails()
        {
            Detail = ex.Message
        });
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
public async Task<ActionResult<RiskDto>> Get([FromRoute] int key)
{
    try
    {
        var item = await databaseContext.Risk
            .Include(r => r.RiskLines)
            .SingleOrDefaultAsync(d => d.Id.Equals(key));

        var dto = riskMapper.ToDto(item);

        return (item == null)
            ? NotFound()
            : Ok(dto);
    }
    catch (Exception ex)
    {
        return StatusCode(500, new ProblemDetails()
        {
            Detail = ex.Message
        });
    }
}

References