Ef Core

EF Core Agent

Guides correct Entity Framework Core usage with the Pomelo MariaDB provider.

Provider Setup

NuGet packages:
  Microsoft.EntityFrameworkCore
  Pomelo.EntityFrameworkCore.MySql
  Microsoft.EntityFrameworkCore.Design  ← local dev only; exclude from runtime image

// appsettings.json
{
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Port=3306;Database=<project>_dev;User=<user>;Password=<pass>;AllowPublicKeyRetrieval=true;"
  }
}

// Program.cs
builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseMySql(
        builder.Configuration.GetConnectionString("DefaultConnection"),
        ServerVersion.AutoDetect(builder.Configuration.GetConnectionString("DefaultConnection"))
    )
);

Entity Conventions

public class MyEntity
{
    public Guid Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public DateTime CreatedAt { get; set; }
    public DateTime? UpdatedAt { get; set; }
}

DbContext (primary constructor)

public class ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
    : DbContext(options)
{
    public DbSet<MyEntity> MyEntities => Set<MyEntity>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
    }
} // end ApplicationDbContext

Migration Workflow

# Add
dotnet ef migrations add <MigrationName> --project src/<Project>.Api --startup-project src/<Project>.Api

# Apply
dotnet ef database update --project src/<Project>.Api --startup-project src/<Project>.Api

# List status
dotnet ef migrations list --project src/<Project>.Api

# Rollback to migration
dotnet ef database update <PreviousMigrationName> --project src/<Project>.Api

# Remove last un-applied file (does not touch DB)
dotnet ef migrations remove --project src/<Project>.Api

# Wipe schema — dangerous
dotnet ef database update 0 --project src/<Project>.Api

Naming Conventions

Startup Auto-Migrate

ALWAYS db.Database.Migrate(). NEVER EnsureCreated().

EnsureCreated() creates the schema without the __EFMigrationsHistory table — permanently blocking future migrations on that database.

// Program.cs — after app build, before app.Run()
using (var scope = app.Services.CreateScope())
{
    var db = scope.ServiceProvider.GetRequiredService<ApplicationDbContext>();
    db.Database.Migrate();
}

Linux LINQ ReadOnlySpan Gotcha

Silent bug on Linux that does NOT appear on macOS development.

// ❌ BROKEN on Linux — compiles, throws at EF Core runtime
var results = db.Entities
    .Where(e => new[] { "active", "pending" }.Contains(e.Status))
    .ToList();

// ✅ CORRECT — declare as List<string> explicitly
var statuses = new List<string> { "active", "pending" };
var results = db.Entities
    .Where(e => statuses.Contains(e.Status))
    .ToList();

Rule: Always use List<string> (not string[]) for collections used in LINQ .Contains() against EF Core queries.

Service Layer Pattern

Controllers are thin — all logic in scoped services behind interfaces.

// IMyService.cs
public interface IMyService
{
    Task<MyDto[]> GetAllAsync();
    Task<MyDto> GetByIdAsync(Guid id);       // throws NotFoundException
    Task<MyDto> CreateAsync(CreateRequest r);
    Task<MyDto> UpdateAsync(Guid id, UpdateRequest r);
    Task DeleteAsync(Guid id);
}

// MyService.cs
public class MyService(ApplicationDbContext db) : IMyService
{
    // ── QUERY ─────────────────────────────────────────────────────────────

    // returns all entities sorted by name
    public async Task<MyDto[]> GetAllAsync() =>
        await db.MyEntities
            .OrderBy(e => e.Name)
            .Select(e => new MyDto { Id = e.Id, Name = e.Name })
            .ToArrayAsync();

    // throws NotFoundException if not found
    public async Task<MyDto> GetByIdAsync(Guid id) =>
        await db.MyEntities
            .Where(e => e.Id == id)
            .Select(e => new MyDto { Id = e.Id, Name = e.Name })
            .FirstOrDefaultAsync()
        ?? throw new NotFoundException($"MyEntity {id} not found");
} // end MyService

// Program.cs registration
builder.Services.AddScoped<IMyService, MyService>();

Domain Exceptions (→ RFC 7807 ProblemDetails)

Testing

// In-memory DB for unit tests (not real MariaDB)
services.AddDbContext<ApplicationDbContext>(o =>
    o.UseInMemoryDatabase("TestDb_" + Guid.NewGuid()));

Never test against real MariaDB from CI unit tests — use in-memory for portability.

EF_KNOWLEDGE

Append new EF Core discoveries here. Format: YYYY-MM-DD: <discovery>

• 2026-02-27: [HelloNetworkWorld] Pomelo TCP connection on macOS Homebrew MariaDB — socket path auth not needed with password-authenticated user. Connection uses Server=localhost;Port=3306;.
• 2026-02-27: [HelloNetworkWorld] db.Database.Migrate() in Program.cs — InitialCreate migration applied cleanly on hnw_dev DB on first run.
• 2026-02-27: [DSC-modernization] Quartz.NET background job scheduler requires its own AddQuartz() DI extension. Keep Quartz config separate from EF Core registration to avoid Program.cs bloat.