How to Use MySQL Database with Microsoft .NET

I’ve built several production APIs on .NET backed by MySQL, and one thing I appreciate is how mature the tooling has become — Entity Framework Core’s MySQL provider, connection pooling, and migrations all work smoothly together once you know the right pieces to wire up. In this article, I’ll walk through the full journey: setting up a .NET project with MySQL, understanding what’s happening under the hood in both the ORM and the database engine, and building toward production-grade patterns for performance, security, and troubleshooting.

Table of Contents

1. MySQL Architecture Fundamentals
2. Why Use MySQL with .NET
3. Setting Up the Project
4. Connecting to MySQL with ADO.NET
5. Using Entity Framework Core with MySQL
6. Defining Models and Running Migrations
7. CRUD Operations with EF Core
8. Raw SQL and Stored Procedures from .NET
9. Transactions and Concurrency
10. Connection Pooling and Performance
11. Indexing and Query Optimization
12. Security Best Practices
13. Troubleshooting Common Issues
14. Interview Questions
15. FAQs
16. Summary and Key Takeaways
17. References

1. MySQL Architecture Fundamentals

Regardless of which language sits on top, MySQL’s core architecture stays the same: a connection layer handles authentication and session state, the SQL layer parses and optimizes queries, and the InnoDB storage engine manages the actual data through its buffer pool, redo log, and tablespace files.

graph TD
    A[.NET Application] --> B[MySQL Connector/NET or Pomelo Provider]
    B --> C[MySQL Server - Connection Layer]
    C --> D[SQL Parser & Optimizer]
    D --> E[InnoDB Storage Engine]
    E --> F[Buffer Pool]
    E --> G[Tablespace Files]

Knowing this matters in .NET specifically because the connector library (MySqlConnector or Oracle’s MySql.Data) is what translates .NET’s ADO.NET abstractions into the MySQL wire protocol — and picking the right one affects both performance and how well async operations behave under load.

2. Why Use MySQL with .NET

I choose this stack when a team already has strong .NET expertise but wants an open-source, cost-effective relational database rather than committing to SQL Server licensing. It works well for web APIs, background services, and internal tools alike, and Entity Framework Core’s MySQL support has matured enough that I rarely miss SQL Server-specific features for typical CRUD-heavy applications.

3. Setting Up the Project

I usually start a new Web API project like this:

dotnet new webapi -n MySqlDemoApi
cd MySqlDemoApi

Installing the MySQL provider — I prefer Pomelo.EntityFrameworkCore.MySql over Oracle’s official EF provider because it tends to track EF Core releases faster and has broader community support:

dotnet add package Pomelo.EntityFrameworkCore.MySql
dotnet add package Microsoft.EntityFrameworkCore.Design

For raw ADO.NET access, I also add:

dotnet add package MySqlConnector

4. Connecting to MySQL with ADO.NET

Before bringing in EF Core, it’s worth understanding the raw connection layer, since EF Core is ultimately built on top of it.

using MySqlConnector;

var connectionString = "Server=localhost;Port=3306;Database=appdb;User=appuser;Password=AppPass123!;";

await using var connection = new MySqlConnection(connectionString);
await connection.OpenAsync();

await using var command = new MySqlCommand("SELECT VERSION();", connection);
var version = await command.ExecuteScalarAsync();
Console.WriteLine($"MySQL Version: {version}");

Expected output:

MySQL Version: 8.0.36

I store the connection string in appsettings.json rather than in code:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Port=3306;Database=appdb;User=appuser;Password=AppPass123!;"
  }
}

5. Using Entity Framework Core with MySQL

Registering the DbContext in Program.cs:

using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);

var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseMySql(connectionString, ServerVersion.AutoDetect(connectionString)));

var app = builder.Build();

ServerVersion.AutoDetect queries the server once at startup to determine the correct SQL dialect features to target — I always use this rather than hardcoding a version, since it avoids subtle incompatibilities if the server gets upgraded later.

6. Defining Models and Running Migrations

A simple model:

public class Order
{
    public int Id { get; set; }
    public int CustomerId { get; set; }
    public decimal Total { get; set; }
    public DateTime CreatedAt { get; set; } = DateTime.UtcNow;
}

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }
    public DbSet<Order> Orders => Set<Order>();
}

Creating and applying a migration:

dotnet tool install --global dotnet-ef
dotnet ef migrations add InitialCreate
dotnet ef database update

The generated migration produces SQL similar to this against MySQL:

CREATE TABLE `Orders` (
    `Id` int NOT NULL AUTO_INCREMENT,
    `CustomerId` int NOT NULL,
    `Total` decimal(65,30) NOT NULL,
    `CreatedAt` datetime(6) NOT NULL,
    PRIMARY KEY (`Id`)
) CHARACTER SET=utf8mb4;

I always double-check the generated decimal precision — EF Core’s default of decimal(65,30) is rarely what I want for currency values, so I explicitly configure it:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Order>()
        .Property(o => o.Total)
        .HasPrecision(10, 2);
}

7. CRUD Operations with EF Core

// Create
var order = new Order { CustomerId = 1042, Total = 49.99m };
context.Orders.Add(order);
await context.SaveChangesAsync();

// Read
var orders = await context.Orders
    .Where(o => o.CustomerId == 1042)
    .OrderByDescending(o => o.CreatedAt)
    .ToListAsync();

// Update
var existing = await context.Orders.FindAsync(order.Id);
existing.Total = 59.99m;
await context.SaveChangesAsync();

// Delete
context.Orders.Remove(existing);
await context.SaveChangesAsync();

The LINQ query above translates to something like this SQL under the hood, which I regularly check with EF Core logging enabled:

SELECT `o`.`Id`, `o`.`CustomerId`, `o`.`Total`, `o`.`CreatedAt`
FROM `Orders` AS `o`
WHERE `o`.`CustomerId` = 1042
ORDER BY `o`.`CreatedAt` DESC;

8. Raw SQL and Stored Procedures from .NET

For reporting queries or performance-critical paths, I sometimes bypass LINQ and run raw SQL directly through EF Core:

var topCustomers = await context.Orders
    .FromSqlRaw(@"
        SELECT CustomerId, SUM(Total) AS Total, CreatedAt
        FROM Orders
        GROUP BY CustomerId
        ORDER BY SUM(Total) DESC
        LIMIT 10")
    .ToListAsync();

Calling a stored procedure:

DELIMITER $$
CREATE PROCEDURE GetCustomerOrderSummary(IN custId INT)
BEGIN
    SELECT COUNT(*) AS order_count, SUM(total) AS total_spent
    FROM orders
    WHERE customer_id = custId;
END$$
DELIMITER ;

await using var command = new MySqlCommand("GetCustomerOrderSummary", connection)
{
    CommandType = CommandType.StoredProcedure
};
command.Parameters.AddWithValue("custId", 1042);

await using var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
    Console.WriteLine($"Orders: {reader["order_count"]}, Total: {reader["total_spent"]}");
}

9. Transactions and Concurrency

For multi-step operations that must succeed or fail together, I wrap them in an explicit transaction:

await using var transaction = await context.Database.BeginTransactionAsync();
try
{
    context.Orders.Add(new Order { CustomerId = 1042, Total = 25.00m });
    await context.SaveChangesAsync();

    context.Orders.Add(new Order { CustomerId = 1042, Total = 15.00m });
    await context.SaveChangesAsync();

    await transaction.CommitAsync();
}
catch
{
    await transaction.RollbackAsync();
    throw;
}

sequenceDiagram
    participant App as .NET App
    participant EF as EF Core
    participant MySQL
    App->>EF: BeginTransactionAsync
    EF->>MySQL: START TRANSACTION
    App->>EF: SaveChangesAsync (insert 1)
    EF->>MySQL: INSERT INTO orders...
    App->>EF: SaveChangesAsync (insert 2)
    EF->>MySQL: INSERT INTO orders...
    App->>EF: CommitAsync
    EF->>MySQL: COMMIT

For concurrent updates, I add a concurrency token to detect conflicting writes:

public class Order
{
    // ...
    [Timestamp]
    public byte[] RowVersion { get; set; }
}

EF Core throws a DbUpdateConcurrencyException if another process modified the row since it was loaded, which I catch and handle explicitly rather than silently overwriting data.

10. Connection Pooling and Performance

MySqlConnector pools connections by default, and I tune the pool size directly in the connection string based on expected concurrent load:

Server=localhost;Port=3306;Database=appdb;User=appuser;Password=AppPass123!;
Minimum Pool Size=5;Maximum Pool Size=100;ConnectionLifeTime=300;

I also register the DbContext as scoped (the EF Core default in ASP.NET Core), never as a singleton, since DbContext isn’t thread-safe and reusing one instance across concurrent requests leads to subtle, hard-to-reproduce bugs.

11. Indexing and Query Optimization

I define indexes directly through EF Core’s fluent API so they’re captured in migrations rather than added manually and forgotten:

modelBuilder.Entity<Order>()
    .HasIndex(o => o.CustomerId)
    .HasDatabaseName("idx_customer_id");

Generated SQL:

CREATE INDEX `idx_customer_id` ON `Orders` (`CustomerId`);

Before trusting any query in production, I check the plan:

EXPLAIN SELECT * FROM Orders WHERE CustomerId = 1042;

12. Security Best Practices

• Store connection strings in User Secrets locally and Azure Key Vault or environment variables in production — never commit them to source control.
• Always use parameterized queries or EF Core’s LINQ translation; never concatenate user input into raw SQL strings.
• Grant the application’s MySQL user only the privileges it needs — typically SELECT, INSERT, UPDATE, DELETE, never SUPER or GRANT OPTION.
• Enforce TLS for connections crossing an untrusted network by adding SslMode=Required to the connection string.

var badQuery = $"SELECT * FROM Orders WHERE CustomerId = {userInput}"; // never do this

var safeQuery = await context.Orders
    .Where(o => o.CustomerId == userInputAsInt)
    .ToListAsync(); // EF Core parameterizes this automatically

CREATE USER 'appuser'@'%' IDENTIFIED BY 'AppPass123!';
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'appuser'@'%';
FLUSH PRIVILEGES;

12.5 Logging and Testing Database Access in .NET

I always enable EF Core’s SQL logging in development so I can see exactly what’s being sent to MySQL, rather than guessing at what a LINQ expression compiles to:

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseMySql(connectionString, ServerVersion.AutoDetect(connectionString))
           .LogTo(Console.WriteLine, LogLevel.Information)
           .EnableSensitiveDataLogging());

I keep EnableSensitiveDataLogging() restricted to local development only, since it prints actual parameter values — including anything sensitive — directly into logs.

For integration tests, I spin up a real MySQL instance in a Docker container using Testcontainers rather than mocking the database entirely, since I’ve found that mocked repositories tend to hide real SQL and mapping bugs until production.

dotnet add package Testcontainers.MySql

public class OrderRepositoryTests : IAsyncLifetime
{
    private readonly MySqlContainer _mysqlContainer = new MySqlBuilder()
        .WithImage("mysql:8.0")
        .WithDatabase("testdb")
        .Build();

    public async Task InitializeAsync() => await _mysqlContainer.StartAsync();
    public async Task DisposeAsync() => await _mysqlContainer.DisposeAsync();

    [Fact]
    public async Task AddOrder_PersistsToDatabase()
    {
        var options = new DbContextOptionsBuilder<AppDbContext>()
            .UseMySql(_mysqlContainer.GetConnectionString(), ServerVersion.AutoDetect(_mysqlContainer.GetConnectionString()))
            .Options;

        await using var context = new AppDbContext(options);
        await context.Database.MigrateAsync();

        context.Orders.Add(new Order { CustomerId = 1, Total = 20.00m });
        await context.SaveChangesAsync();

        Assert.Equal(1, await context.Orders.CountAsync());
    }
}

This gives me real confidence that migrations, mappings, and queries all work against an actual MySQL engine, not just an in-memory substitute that might silently accept invalid SQL semantics.

12.6 Health Checks for Production Reliability

For any .NET service backed by MySQL, I add a health check endpoint so orchestrators like Kubernetes or Azure App Service can detect a database outage and react accordingly:

dotnet add package AspNetCore.HealthChecks.MySql

builder.Services.AddHealthChecks()
    .AddMySql(connectionString, name: "mysql", timeout: TimeSpan.FromSeconds(5));

app.MapHealthChecks("/health");

curl http://localhost:5000/health

Healthy

I’ve found this small addition catches connectivity issues — expired credentials, network partitions, exhausted connection pools — well before they surface as confusing 500 errors reported by end users.

13. Troubleshooting Common Issues

14. Interview Questions

1. Why is ServerVersion.AutoDetect recommended over hardcoding a MySQL version string in EF Core?
2. What’s the difference between Pomelo.EntityFrameworkCore.MySql and Oracle’s official EF Core provider?
3. Why should DbContext never be registered as a singleton in ASP.NET Core?
4. How does EF Core detect concurrent update conflicts, and how would you handle them?
5. Why is it dangerous to concatenate user input into a raw SQL string, and how does EF Core avoid this by default?
6. How would you diagnose a slow query originating from an EF Core LINQ statement?

15. FAQs

Should I use Entity Framework Core or raw ADO.NET with MySQL? I use EF Core for most CRUD-heavy application code and drop down to raw SQL or stored procedures only for performance-critical reporting queries.

Which MySQL connector library should I use for .NET? I prefer MySqlConnector (and the Pomelo EF Core provider built on it) over Oracle’s official connector, mainly for its async performance and faster compatibility updates.

How do I handle decimal precision issues in migrations? Explicitly configure precision with HasPrecision() in OnModelCreating rather than relying on EF Core’s default.

Is MySQL a good fit for a .NET enterprise application? Yes, especially when cost or licensing flexibility matters; EF Core’s MySQL support is mature enough for most typical enterprise CRUD workloads.

16. Summary and Key Takeaways

MySQL and .NET work well together once a few key decisions are made correctly: choose MySqlConnector/Pomelo for the provider, always use ServerVersion.AutoDetect, explicitly set decimal precision, register DbContext as scoped, and rely on parameterized queries or LINQ rather than raw string concatenation. From there, EF Core migrations, indexing, and transaction handling follow familiar .NET patterns, with MySQL’s InnoDB engine doing the heavy lifting underneath.

17. References

• MySQL 8.0 Reference Manual
• Entity Framework Core Documentation
• Pomelo.EntityFrameworkCore.MySql GitHub Repository
• MySqlConnector Documentation