CodeLogic.MySQL2
4.5.2-preview.68
See the version list below for details.
dotnet add package CodeLogic.MySQL2 --version 4.5.2-preview.68
NuGet\Install-Package CodeLogic.MySQL2 -Version 4.5.2-preview.68
<PackageReference Include="CodeLogic.MySQL2" Version="4.5.2-preview.68" />
<PackageVersion Include="CodeLogic.MySQL2" Version="4.5.2-preview.68" />
<PackageReference Include="CodeLogic.MySQL2" />
paket add CodeLogic.MySQL2 --version 4.5.2-preview.68
#r "nuget: CodeLogic.MySQL2, 4.5.2-preview.68"
#:package CodeLogic.MySQL2@4.5.2-preview.68
#addin nuget:?package=CodeLogic.MySQL2&version=4.5.2-preview.68&prerelease
#tool nuget:?package=CodeLogic.MySQL2&version=4.5.2-preview.68&prerelease
CodeLogic.MySQL2
v4.0.0 — major rewrite. Typed LINQ translated to SQL, compiled row materializers, working result cache (time-quantized keys + table-version invalidation), SQL-side aggregation (
GroupBy+ aggregatingSelect), projection pushdown, covering indexes, attribute-driven retention. See the Performance docs for benchmark numbers.
MySQL / Percona / MariaDB library for CodeLogic. Typed LINQ-shaped queries translated to SQL, compiled row materializers, working result cache, server-side aggregation, and covering indexes driven by attributes. Built on MySqlConnector.
Install
dotnet add package CodeLogic.MySQL2
Quick start
await Libraries.LoadAsync<MySQL2Library>();
var mysql = Libraries.Get<MySQL2Library>();
// CRUD via the repository
var repo = mysql.GetRepository<UserRecord>();
await repo.InsertAsync(new UserRecord { Name = "Alice", Email = "alice@example.com" });
// Typed LINQ — translated to SQL
var activeAdmins = await mysql.Query<UserRecord>()
.Where(u => u.IsActive && u.Role == "admin")
.OrderBy(u => u.Name)
.WithCache(TimeSpan.FromMinutes(5))
.ToPagedListAsync(page: 1, pageSize: 20);
What's in the box
Typed query builder
Full expression translation to SQL — no magic strings in consumer code.
| Capability | Shape |
|---|---|
| Filter | .Where(x => x.Status == "active" && x.Age >= 18) |
| Sort | .OrderBy, .OrderByDescending, .ThenBy, .ThenByDescending |
| Paging | .Take, .Skip, .ToPagedListAsync |
| String ops | Contains / StartsWith / EndsWith → LIKE (escaped) |
| IN | list.Contains(x.Col) → x.Col IN (...) |
| Null | x.Col == null → IS NULL; string.IsNullOrEmpty(x.Col) too |
| Nullable | x.NullableCol.Value passthrough |
| Join | .Join<TRight, TKey, TResult>(leftKey, rightKey, resultSelector) → typed equi-join |
| Subquery | .WhereExists<TInner>((o, i) => …), .WhereIn<TInner, TKey>(col, innerCol, filter?) |
Typed JOINs (new in 4.5.2)
Join<TRight, TKey, TResult> translates a strongly-typed equi-join to real SQL
with table aliases and a compiled projection — only the columns the result
selector references cross the wire.
var views = await mysql.Query<Order>()
.Where(o => o.Total > 100) // carried over, re-qualified to the left table
.Join<Customer, long, OrderView>(
o => o.CustomerId, // left key
c => c.Id, // right key
(o, c) => new OrderView { OrderId = o.Id, Customer = c.Name })
.OrderByDescending((o, c) => o.Total)
.Take(20)
.ToListAsync();
- Join types:
Inner(default),Left,Right. - Composite keys:
o => new { o.A, o.B }matched positionally withc => new { c.X, c.Y }. - Fluent surface on the join:
.Where((l, r) => …),.OrderBy/.OrderByDescending,.Take/.Skip, andToListAsync/FirstOrDefaultAsync/CountAsync. TRightis explicit (Join<Customer, long, OrderView>) — it can't be inferred from a lambda parameter.- Not cacheable yet — the result cache versions entries by a single table, so a
join can't be safely invalidated when the other table mutates.
.WithCache/.SmartCacheare intentionally absent on the join. Apply ordering/paging after.Join. The raw-stringJoin(table, condition, type)overload is still available for hand-written joins.
Subquery filters — EXISTS / IN (new in 4.5.2)
Correlated and uncorrelated subqueries in the WHERE clause, without dropping to raw SQL:
// Orders that have at least one sent shipment (correlated EXISTS)
await mysql.Query<Order>()
.WhereExists<Shipment>((o, s) => s.OrderId == o.Id && s.Status == "sent")
.ToListAsync();
// Orders whose customer is a VIP (IN over a filtered subquery)
await mysql.Query<Order>()
.WhereIn<Customer, long>(o => o.CustomerId, c => c.Id, c => c.IsVip)
.ToListAsync();
| Method | SQL |
|---|---|
WhereExists<TInner>((o, i) => …) |
EXISTS (SELECT 1 FROM inner WHERE …) |
WhereNotExists<TInner>(…) |
NOT EXISTS (…) |
WhereIn<TInner, TKey>(col, innerCol, filter?) |
col IN (SELECT innerCol FROM inner [WHERE …]) |
WhereNotIn<TInner, TKey>(…) |
col NOT IN (…) |
Subquery filters compose with ordinary .Where(...). They are not cacheable
(the cache can't track the inner table for invalidation) and can't be combined
with a typed .Join. WhereExists against the outer query's own table is rejected.
Raw SQL escape hatch (new in 4.5.2)
For the rare query the translator can't express, drop to SQL without losing the compiled materializer, observability, or the transient-retry policy:
var rows = await mysql.SqlQueryAsync<UserRecord>(
"SELECT * FROM users WHERE country = @c", new() { ["@c"] = "DK" }); // materialized rows
var n = await mysql.SqlScalarAsync<long>("SELECT COUNT(*) FROM users"); // single value
var hit = await mysql.ExecuteSqlAsync("UPDATE users SET active = 0 WHERE last_seen < @t",
new() { ["@t"] = cutoff }); // affected count
Always pass values via named parameters — never interpolate user input into the SQL. Raw queries are not cached.
Soft deletes (new in 4.5.2)
Mark a nullable-DateTime column with [SoftDelete] and deletes become timestamp
updates; reads hide the deleted rows automatically.
[Table(Name = "accounts")]
[SoftDelete(nameof(DeletedUtc))]
public sealed class Account
{
[Column(DataType = DataType.BigInt, Primary = true, AutoIncrement = true)] public long Id { get; set; }
[Column(Name = "deleted_utc", DataType = DataType.DateTime)] public DateTime? DeletedUtc { get; set; }
}
var repo = mysql.GetRepository<Account>();
await repo.DeleteAsync(id); // soft: sets deleted_utc = UtcNow
await mysql.Query<Account>().ToListAsync(); // excludes soft-deleted rows
await mysql.Query<Account>().IncludeDeleted().ToListAsync(); // includes them
await repo.HardDeleteAsync(id); // physical DELETE
Auto-filtering covers single-table mysql.Query<T>() reads and the repository getters.
It does not apply to joins, subqueries, or the query builder's bulk
UpdateAsync/DeleteAsync — those stay raw so you can restore or hard-purge deleted
rows. To restore: Query<Account>().Where(a => a.Id == id).UpdateAsync(a => new Account { DeletedUtc = null }).
Projection pushdown
.Select<TResult>(x => new Foo(x.A, x.B)) emits a real column list — only the
columns referenced are transferred from the DB, not SELECT *.
var lean = await mysql.Query<PostRecord>()
.Where(p => p.PublishedUtc >= since)
.Select(p => new { p.Id, p.Title, p.Slug }) // ships 3 columns, not 15
.ToListAsync();
SQL aggregation (GroupBy → Select)
GroupBy + aggregating Select translate to real GROUP BY + SUM / AVG /
COUNT / MIN / MAX / ANY on the server. No rows materialize client-side.
var heatmap = await mysql.Query<SnapshotRecord>()
.Where(s => s.SnapshotUtc >= since)
.GroupBy(s => new { Dow = SqlFn.DayOfWeek(s.SnapshotUtc),
Hour = SqlFn.Hour(s.SnapshotUtc) })
.Select(g => new HeatmapCell(
g.Key.Dow,
g.Key.Hour,
g.Average(x => (double)x.PlayerCount)))
.ToListAsync();
Inside Select(g => ...) you can call:
g.Key, g.Key.Member, g.Sum(x => ...), g.Average(x => ...), g.Min, g.Max,
g.Count(), g.Count(x => pred), g.Any(), g.Any(pred).
Ternary inside aggregates becomes CASE WHEN:
g.Sum(x => x.IsOnline ? 1 : 0) → SUM(CASE WHEN is_online THEN 1 ELSE 0 END).
SqlFn helpers
SQL function markers recognised by the translator (same pattern as EF.Functions):
| Group | Methods |
|---|---|
| Date/time | Year, Month, Day, Hour, Minute, DayOfWeek (0=Sun..6=Sat), Date, BucketUtc(d, seconds) |
| Conditional | Coalesce(...), IfNull(v, fallback) |
| String | Lower, Upper, Concat(...), Like(s, pattern) |
| Math | Round(v, digits), Floor, Ceiling |
Result cache (now actually working)
.WithCache(TimeSpan.FromMinutes(5))
Two things that weren't right before and now are:
- DateTime closures near "now" are time-quantized — a
.Where(x => x.At >= UtcNow.AddDays(-30))predicate no longer produces a unique cache key per call. The window is configurable (CacheConfiguration.TimeQuantizeSeconds, default 60s). - Table-version invalidation — mutations bump a per-table counter baked into the key. No eviction loop; old keys simply become un-hittable.
Cache hits and misses publish CacheHitEvent / CacheMissEvent on the
CodeLogic event bus.
Stampede protection (new in 4.5.2) — concurrent misses on the same cold key collapse to a single DB hit (single-flight); the rest await that one execution.
Smart cache pools — kept warm in the background (new in 4.2)
For pages where a small set of queries should stay hot regardless of read traffic, register a named pool with a refresh interval and opt queries into it. The pool's background timer re-runs every registered query and overwrites the cache entry — readers never block on the DB after the first populate.
// Declare the pool once at startup (typically in OnInitializeAsync).
mysql.RegisterCachePool("dashboard", refreshEvery: TimeSpan.FromSeconds(30));
// Opt queries into the pool. First call: cold DB hit, result cached, query
// registered with the pool. Subsequent calls: cache hit. Every 30s the
// pool's timer re-runs the query and refreshes the entry.
var top10 = await mysql.Query<PlayerRecord>()
.Where(p => p.IsActive)
.OrderByDescending(p => p.Score)
.Take(10)
.SmartCache("dashboard")
.ToListAsync();
// Out-of-schedule refresh — useful right after a deploy to prime the cache.
await mysql.RefreshCachePoolAsync("dashboard");
// Warm the cache on startup — readers never see a cold pool. The warm-up
// callback just calls the queries that should be hot; they auto-register
// with the pool via .SmartCache("dashboard") as usual. Runs as a fire-and-
// forget task so startup doesn't block.
mysql.RegisterCachePool("dashboard", TimeSpan.FromSeconds(30),
warmUp: async () =>
{
await statsService.GetDashboardTop10Async();
await statsService.GetRecentMatchesAsync();
});
// Diagnostic snapshot
foreach (var s in mysql.GetCachePoolStats())
Console.WriteLine($"{s.Name}: {s.EntryCount} entries, {s.TicksFired} ticks fired");
How it behaves:
- TTL is derived from the pool —
refreshEvery * 2. Cache entries outlive a missed refresh by one cycle before falling back to cache-aside. - Bounded cardinality — an entry that has not been read for
MaxIdleFires(default 3) consecutive ticks is dropped from the refresh list. Parameterized queries (per-user keys, etc.) don't spawn unbounded background work — they auto-retire when nobody's looking. - Mutually exclusive with
.WithCache(TimeSpan)— if both are set, the pool wins. - Falls back gracefully — an unknown pool name on
.SmartCache(name)logs a warning and the query runs uncached. No exception. - Skipped inside transactions — same rule as
.WithCache. - Multi-node coordination (new in 4.5.2) — install an
ICacheCoordinatorviaQueryCache.UseCoordinator(...)so mutations fan out to peers and pool refreshes run single-flight (only the lease-holding node hits the DB). Pair it with a sharedICacheStore(Redis). Without a coordinator the default is single-node — every node runs its own refresh timer, which is safe but wasteful at high node counts.
Bulk writes / predicate mutations
// Real batched INSERT ... VALUES (...), (...), ... (configurable chunk size)
await repo.InsertManyAsync(rows);
// Typed bulk update — one UPDATE statement, values or column expressions
await mysql.Query<TicketRecord>()
.Where(t => t.Status == "open" && t.CreatedUtc < cutoff)
.UpdateAsync(t => new TicketRecord { Status = "stale", Counter = t.Counter + 1 });
// Bulk delete by predicate
await mysql.Query<SnapshotRecord>()
.Where(s => s.SnapshotUtc < cutoff)
.DeleteAsync();
// Upsert — INSERT ... ON DUPLICATE KEY UPDATE
await repo.UpsertAsync(row);
await repo.UpsertManyAsync(rows);
await repo.UpsertWithIncrementsAsync(
seed,
incrementProperties: new[] { nameof(Counter.Hits) }); // col = col + new.col on conflict
Portable upserts (fixed in 4.5.3) — the upsert methods emit the
VALUES(col)conflict form, which works on both MySQL and MariaDB (the olderINSERT ... AS newrow-alias syntax was MySQL-8.0.19+ only and MariaDB rejected it).
Schema sync — three modes + CRC fast-path (new in 4.5.3)
Record classes are the source of truth. SyncTableAsync<T>() creates / alters
MySQL tables to match; SyncSchemaAsync(params Type[]) reconciles a whole set in
one pass under a single cross-node lock (the recommended startup entry point).
await mysql.SyncSchemaAsync(typeof(UserRecord), typeof(OrderRecord), typeof(SnapshotRecord));
A new operator-facing SyncMode (per database) governs how aggressively sync
reconciles:
| Mode | Behaviour |
|---|---|
Developer |
Aggressive rolling updates — drops removed columns/indexes/FKs every boot. |
Production (default) |
Additive only — adds/modifies, never drops. A change needing a drop is deferred and the table flagged DriftPending. |
Migration |
One-shot destructive reconcile (backup first). Idempotent; once everything matches it logs a warning to switch back to Production. |
SyncMode supersedes the legacy SchemaSyncLevel / AllowDestructiveSync knobs
(still honoured for back-compat). Flip the mode at runtime with
mysql.SetSyncMode(SyncMode.Production).
CRC sentinel — __schema_state. Each model's desired schema is hashed (CRC)
into a per-table row. Sync skips a table entirely — no information_schema
diffing, no DDL — when the stored CRC matches the model and the table still
exists. SyncResult carries Skipped, SchemaCrc, and DriftPending; inspect
state via mysql.SchemaState.
Cross-node lock. Schema/migration passes serialize across nodes via MySQL
GET_LOCK — booting a cluster, only one node runs the DDL while the rest wait and
then no-op on matching CRCs.
Imperative migrations (new in 4.5.3)
For data transforms, seeds, and semantic changes the declarative sync can't
express, write an IMigration (or subclass the Migration base):
public sealed class SeedRoles() : Migration("1.4.0", order: 1, "Seed default roles")
{
public override async Task UpAsync(IMigrationContext ctx, CancellationToken ct) =>
await ctx.ExecuteAsync("INSERT INTO roles (name) VALUES ('admin'), ('user')", ct: ct);
// Override DownAsync to make the migration reversible (default throws).
public override async Task DownAsync(IMigrationContext ctx, CancellationToken ct) =>
await ctx.ExecuteAsync("DELETE FROM roles WHERE name IN ('admin','user')", ct: ct);
}
mysql.RegisterMigration(new SeedRoles()); // or RegisterMigrationsFrom(assembly)
await mysql.MigrateAsync(); // apply all pending, in order
var pending = await mysql.GetPendingMigrationsAsync();
await mysql.RollbackAsync(new MigrationVersion("1.3.0", 0)); // undo everything newer
- Migrations run in
MigrationVersion(app-version, then order) sequence, each in its own transaction, under the shared schema-sync lock, gated by the app version. IMigrationContextexposesExecuteAsync,QueryAsync<T>,ScalarAsync<T>, plusSyncTableAsync<T>()to bring a table to its model shape inside the step.- Tracked in the
__migrationstable; an edited-after-apply body is detected by checksum drift and warned about. RollbackAsyncrunsDownAsyncnewest-first and aborts cleanly before any change if a migration in range has noDownAsyncoverride. To roll back a declarative table instead,mysql.RestoreSchemaAsync(tableName)replays a schema backup (DDL only) and clears its__schema_staterow.
MySQL implicitly commits on DDL — a migration mixing
ALTERwith data changes is not atomic, so keepUpAsyncsteps idempotent.
Schema sync driven by attributes
Record classes are the source of truth. SyncTableAsync<T>() creates / alters
MySQL tables to match.
[Table(Name = "servers_snapshot")]
[RetainDays(90, nameof(SnapshotUtc))] // daily background purge
[CompositeIndex("ix_server_snapshot", "server_id", "snapshot_utc")]
public sealed class SnapshotRecord
{
[Column(DataType = DataType.BigInt, Primary = true, AutoIncrement = true)]
public long Id { get; set; }
[Column(Name = "server_id", DataType = DataType.BigInt, NotNull = true)]
public long ServerId { get; set; }
[Column(Name = "snapshot_utc", DataType = DataType.DateTime, NotNull = true)]
[Index(Name = "ix_snapshot_utc_covering",
Include = new[] { nameof(ServerId), nameof(PlayerCount) })] // covering index
public DateTime SnapshotUtc { get; set; }
[Column(Name = "player_count", DataType = DataType.Int, NotNull = true)]
public int PlayerCount { get; set; }
}
Attributes supported:
| Attribute | Purpose |
|---|---|
[Table] |
table name, engine, charset, collation, comment |
[Column] |
type, size, nullability, default, PK/AI/Unique/Index, PreviousName (rename) |
[Index] |
new — named, unique, covering (Include) |
[CompositeIndex] |
multi-column named index on the class |
[ForeignKey] |
FK with ON DELETE/UPDATE actions |
[RetainDays] |
new — background purge job for time-series tables |
[Ignore] |
skip property for schema / read / write |
Sync modes (SyncMode, per database — see above): Production (default,
additive) · Developer (drops freely) · Migration (one-shot destructive). These
map onto the legacy lower-level SchemaSyncLevel (None · Safe · Additive ·
Full), which is retained for back-compat — SyncMode takes precedence.
Renaming a column (new in 4.5.2) — set PreviousName so the rename preserves data:
[Column(Name = "email_address", PreviousName = "email")] // → CHANGE COLUMN email email_address …
public string EmailAddress { get; set; } = "";
Without PreviousName, a rename looks like a new column plus an orphaned old one
(and a data-losing DROP at Full). Drop the PreviousName once every environment
has synced.
Observability
The library publishes events to CodeLogic's event bus:
QueryExecutedEvent— every query: SQL, elapsed ms, row count, cache hit flagSlowQueryEvent— queries over the threshold (logged too)CacheHitEvent/CacheMissEvent— per-callN1QueryDetectedEvent— when the detector tripsTableSyncedEvent,DatabaseConnected/Disconnected— lifecycle
Background retention worker
Entities with [RetainDays(N, nameof(TimestampCol))] are picked up automatically
when registered via SyncTableAsync<T>(). A daily pass runs batched
DELETE WHERE {col} < NOW() - INTERVAL N DAY LIMIT batchSize until drained.
Configuration
Two config sections are auto-generated on first run under
data/codelogic/Libraries/CL.MySQL2/.
config.mysql.json — per-database settings. Highlights:
| Field | Default | Purpose |
|---|---|---|
Host, Port, Database, Username, Password |
— | connection |
EnablePooling, MinPoolSize, MaxPoolSize, ConnectionLifetime |
true/1/100/300s | pool |
SyncMode |
Production |
Developer / Production / Migration — primary schema-sync knob |
SchemaSyncLevel |
Safe |
Legacy. None / Safe / Additive / Full (superseded by SyncMode) |
SlowQueryThresholdMs |
1000 | slow query logging |
QueryTimeoutMs |
30000 | default per-query timeout |
MaxBatchInsertSize |
500 | InsertManyAsync chunk size |
MaxInClauseValues |
1000 | IN-clause safety cap |
PreparedStatementCacheSize |
256 | per-connection |
TransientRetryCount |
3 | auto-retry deadlock/lock-wait on single statements (0 = off) |
TransientRetryBaseDelayMs |
50 | base backoff for transient retries (exponential + jitter) |
N1DetectorThreshold |
0 (off) | warn on repeated query in request scope |
CaptureExplainOnSlowQuery |
true | attach EXPLAIN to SlowQueryEvent |
DefaultStringSize |
255 | VARCHAR size when no [Column(Size)] |
CacheEnabledOverride |
null (inherit) | per-DB cache on/off |
config.mysql.cache.json — global cache settings. Highlights:
| Field | Default | Purpose |
|---|---|---|
Enabled |
true | master switch |
MaxEntries |
10000 | soft cap |
DefaultTtlSeconds |
60 | when .WithCache() has no TTL |
TimeQuantizeSeconds |
60 | round DateTime params in cache keys |
PublishEvents |
true | emit hit/miss events |
Transactions
await using var tx = await mysql.BeginTransactionAsync();
var repo = mysql.GetRepository<AccountRecord>(tx);
// ... work ...
await tx.CommitAsync(); // auto-rolls back on dispose if not committed
Testing
A console test runner lives at CL.MySQL2/tests/CL.MySQL2.Tests. It boots the
library through the full CodeLogic lifecycle against a local MySQL/MariaDB and
exercises queries, schema sync, the sync modes, migrations, and rollback.
Requirements
- CodeLogic 3.x or 4.x
- .NET 10
- MySQL 5.7+ / MariaDB 10.2+ / Percona 8.x
License
MIT — see LICENSE
| Product | Versions Compatible and additional computed target framework versions. |
|---|---|
| .NET | net10.0 is compatible. net10.0-android was computed. net10.0-browser was computed. net10.0-ios was computed. net10.0-maccatalyst was computed. net10.0-macos was computed. net10.0-tvos was computed. net10.0-windows was computed. |
-
net10.0
- CodeLogic (>= 3.2.0 && < 5.0.0)
- MySqlConnector (>= 2.6.0)
NuGet packages
This package is not used by any NuGet packages.
GitHub repositories
This package is not used by any popular GitHub repositories.
| Version | Downloads | Last Updated |
|---|---|---|
| 4.6.72 | 179 | 6/20/2026 |
| 4.6.70-preview | 38 | 6/20/2026 |
| 4.6.69-preview | 39 | 6/20/2026 |
| 4.5.4 | 134 | 6/5/2026 |
| 4.5.4-preview.59 | 75 | 5/24/2026 |
| 4.5.3 | 109 | 6/5/2026 |
| 4.5.3-preview.58 | 55 | 5/24/2026 |
| 4.5.2 | 117 | 5/24/2026 |
| 4.5.2-preview.68 | 57 | 6/20/2026 |
| 4.5.2-preview.57 | 67 | 5/24/2026 |
| 4.5.1 | 178 | 5/24/2026 |
| 4.5.1-preview.60 | 62 | 5/24/2026 |
| 4.5.1-preview.56 | 69 | 5/24/2026 |
| 4.4.2-preview.53 | 55 | 5/24/2026 |
| 4.4.1 | 110 | 5/24/2026 |
| 4.4.1-preview.55 | 60 | 5/24/2026 |
| 4.4.1-preview.52 | 58 | 5/24/2026 |
| 4.3.0-preview.51 | 63 | 5/24/2026 |
| 4.2.3 | 130 | 5/15/2026 |
| 4.2.2 | 131 | 5/15/2026 |
# CL.MySQL2 — Changelog
All notable changes to **CodeLogic.MySQL2** are documented here. Versions follow
[Semantic Versioning](https://semver.org/). The version listed here matches the
NuGet package version of `CodeLogic.MySQL2`.
## [4.5.3] — 2026-06-20
### Added
- **Three schema sync modes — `SyncMode`.** A new operator-facing knob on each
database (`config.mysql.json`) replaces the lower-level `SchemaSyncLevel` /
`AllowDestructiveSync` flags (which still work for back-compat — `SyncMode` takes
precedence and maps onto them via `EffectiveSyncLevel`).
| Mode | Behaviour |
|---|---|
| `Developer` | Aggressive rolling updates — drops removed columns/indexes/FKs on every boot (maps to `Full`). |
| `Production` *(default)* | Additive only — adds/modifies, **never drops**. A change that needs a drop is deferred and the table is flagged `DriftPending`. |
| `Migration` | Deliberate one-shot destructive reconcile (takes a schema backup first). Idempotent — once every model matches and no drift is pending it does nothing and logs a warning to switch back to `Production`. |
```json
{ "Databases": { "Default": { "SyncMode": "Production" } } }
```
- **CRC sentinel — `__schema_state`.** Each model's desired schema is hashed
(CRC) into a per-table row. Sync skips a table **entirely** — no
`information_schema` diffing, no DDL — when the stored CRC matches the model
*and* the table still exists. New `SyncResult` fields: `Skipped`, `SchemaCrc`,
`DriftPending`; new `SchemaSyncStatus` enum (`Synced` / `DriftPending`).
Exposed via `mysql.SchemaState` (a `SchemaStateStore`).
- **Cross-node schema-sync lock — `SchemaSyncLock`.** A schema/migration pass
serializes across application nodes with MySQL `GET_LOCK`. The winner runs the
DDL; peers wait, then find the schema already reconciled (matching CRCs) and do
nothing.
- **Batch schema sync + runtime mode override.** `mysql.SyncSchemaAsync(params
Type[])` reconciles a whole set of entities as one pass under a single lock,
honouring the configured `SyncMode` and the CRC fast-path — the recommended
startup entry point. `mysql.SetSyncMode(mode, connectionId)` overrides the mode
at runtime (e.g. to flip `Migration` back to `Production` once a pass completes).
- **Imperative migrations.** `IMigration` / `MigrationVersion` / the abstract
`Migration` base for data transforms, seeds, and semantic changes the
declarative sync can't express. `IMigrationContext` provides `ExecuteAsync`,
`QueryAsync<T>`, `ScalarAsync<T>`, and a `SyncTableAsync<T>()` bridge into
declarative sync. The `MigrationRunner` applies pending migrations in
`MigrationVersion` order over the `__migrations` table, each in its own
transaction, under the shared lock, gated by the app version
(`CodeLogicEnvironment.AppVersion`), and warns when an applied migration's
checksum has drifted. Library surface: `RegisterMigration`,
`RegisterMigrationsFrom(assembly)`, `MigrateAsync`, `GetPendingMigrationsAsync`.
```csharp
public sealed class SeedRoles() : Migration("1.4.0", 1, "Seed default roles")
{
public override async Task UpAsync(IMigrationContext ctx, CancellationToken ct) =>
await ctx.ExecuteAsync("INSERT INTO roles (name) VALUES ('admin'), ('user')", ct: ct);
}
```
> MySQL implicitly commits on DDL, so a migration that mixes `ALTER` with data
> changes is not atomic — keep `UpAsync` steps idempotent.
- **Rollback.** `mysql.RollbackAsync(MigrationVersion target)` runs `DownAsync`
newest-first for every applied migration above `target`, each in its own
transaction. It pre-flights the range and aborts cleanly **before any change**
if a migration in range has no `DownAsync` override. Declaratively,
`mysql.RestoreSchemaAsync(tableName)` replays a `BackupManager` schema snapshot
(DDL only — rows are lost) and clears the table's `__schema_state` row so the
next sync reconciles from scratch.
### Fixed
- **Upsert now portable to MariaDB.** `UpsertAsync`, `UpsertManyAsync`, and
`UpsertWithIncrementsAsync` emit the portable `... ON DUPLICATE KEY UPDATE col =
VALUES(col)` form, which works on **both** MySQL and MariaDB, instead of the
MySQL-8.0.19+-only `INSERT ... AS new ... ON DUPLICATE KEY UPDATE` row-alias
syntax that MariaDB rejected.
## [4.5.2] — 2026-06-13
### Added
- **Typed JOINs.** `Query<TLeft>().Join<TRight, TKey, TResult>(leftKey, rightKey,
resultSelector, type)` translates a strongly-typed equi-join to real SQL with
table aliases (left `t0`, right `t1`) and a compiled, reflection-free projection
into `TResult` — only the columns the selector references are transferred.
```csharp
var views = await mysql.Query<Order>()
.Where(o => o.Total > 100)
.Join<Customer, long, OrderView>(
o => o.CustomerId, // left key
c => c.Id, // right key
(o, c) => new OrderView { OrderId = o.Id, Customer = c.Name })
.OrderByDescending((o, c) => o.Total)
.Take(20)
.ToListAsync();
```
- **Join types:** `Inner` (default), `Left`, `Right`. `Cross` is rejected for a
keyed join (keys imply an equi-join).
- **Composite keys:** `o => new { o.A, o.B }` matched positionally with
`c => new { c.X, c.Y }`.
- **Carried filters:** `.Where(...)` calls made on the left builder *before*
`.Join` are re-qualified to the left table and preserved.
- **Fluent surface on the join:** `.Where((l, r) => …)`, `.OrderBy` /
`.OrderByDescending((l, r) => …)`, `.Take` / `.Skip`, and the
`ToListAsync` / `FirstOrDefaultAsync` / `CountAsync` terminals.
- The single-table query path and the existing raw-string
`Join(table, condition, type)` overload are unchanged.
- **Subquery filters — `EXISTS` / `IN`.** Four new WHERE-family methods on the
query builder translate to real SQL subqueries:
```csharp
// Correlated EXISTS — correlated + non-correlated conditions in one predicate
mysql.Query<Order>()
.WhereExists<Shipment>((o, s) => s.OrderId == o.Id && s.Status == "sent");
// IN (subquery) with an optional uncorrelated inner filter
mysql.Query<Order>()
.WhereIn<Customer, long>(o => o.CustomerId, c => c.Id, c => c.IsVip);
```
- `WhereExists<TInner>` / `WhereNotExists<TInner>` →
`[NOT] EXISTS (SELECT 1 FROM inner WHERE …)`, correlated via the predicate.
- `WhereIn<TInner, TKey>` / `WhereNotIn<TInner, TKey>` →
`col [NOT] IN (SELECT innerCol FROM inner [WHERE innerFilter])`.
- Composes with ordinary `.Where(...)` and reuses the same multi-source
translator as joins (each source qualified by its table name).
- **Column rename — `[Column(PreviousName = "old_col")]`.** Schema sync now emits
`CHANGE COLUMN old_col new_col …` to rename in place and **preserve the data**,
instead of the drop-old + add-new that silently lost it (orphan column at Safe;
data loss at Full). Works at `Safe` and above; remove `PreviousName` once every
environment has synced.
```csharp
[Column(Name = "email_address", PreviousName = "email")]
public string EmailAddress { get; set; } = "";
```
- **Multi-node cache coordination — `ICacheCoordinator`.** A pluggable coordination
seam (same model as `ICacheStore`: interface + in-process default, distributed
adapter supplied by the consumer) that closes the single-node limitation called
out in 4.1.2's notes. Install with `QueryCache.UseCoordinator(...)`.
- **Cross-node invalidation** — a local mutation now fans out via
`PublishInvalidationAsync`; a peer's broadcast bumps this node's table-version
counter and evicts matching entries (without re-broadcasting). Previously the
version counter was per-process, so a mutation on one node never invalidated
the others.
- **Single-flight pool refresh** — `SmartCachePool` ticks now acquire a refresh
lease via `TryAcquireRefreshLeaseAsync`; only the lease holder hits the DB, so
N nodes don't all refresh the same pool. Idle-entry retirement still runs on
every node. Pair with a shared `ICacheStore` (e.g. Redis) so non-leaders read
the entry the leader writes.
- The default `NullCacheCoordinator` is single-node: no fan-out, always grants
the lease — behaviour is identical to before off-cluster.
- **Raw SQL escape hatch.** `mysql.SqlQueryAsync<T>(sql, parameters)` materializes
rows into `T` with the same compiled materializer as the query builder;
`ExecuteSqlAsync(sql, parameters)` runs a non-query and returns the affected count;
`SqlScalarAsync<T>(sql, parameters)` returns a single value. All use named
parameters, flow through observability, and inherit the transient-retry policy.
```csharp
var rows = await mysql.SqlQueryAsync<UserRecord>(
"SELECT * FROM users WHERE country = @c", new() { ["@c"] = "DK" });
```
- **Transient-error auto-retry.** Single non-transactional statements that fail with
a deadlock (1213) or lock-wait timeout (1205) are retried with exponential backoff
+ jitter. Configurable per database via `TransientRetryCount` (default 3) and
`TransientRetryBaseDelayMs` (default 50); 0 disables. Statements inside an explicit
transaction scope are never auto-retried — the whole transaction is the caller's
to retry.
- **Cache stampede protection.** Concurrent cache misses on the same cold key now
collapse to a single factory execution (single-flight) instead of a thundering
herd of identical DB queries. Transparent — no API change.
- **Soft deletes — `[SoftDelete(nameof(DeletedUtc))]`.** Marks a nullable-`DateTime`
column as the delete marker. `Repository.DeleteAsync` then sets it to UtcNow
instead of issuing a physical `DELETE`, and reads via `mysql.Query<T>()` and the
repository getters automatically exclude rows where it is set. Opt back in with
`.IncludeDeleted()` on a query, or purge for real with `Repository.HardDeleteAsync`.
```csharp
[SoftDelete(nameof(DeletedUtc))]
public class Account { /* … */ public DateTime? DeletedUtc { get; set; } }
```
### Notes
- **No breaking changes.** Joins and subquery filters are new methods; the
multi-source WHERE translator is byte-identical to the single-table translator
when no alias map is supplied.
- **Subquery-filtered queries are not cacheable** and cannot be turned into a
typed `.Join` — same single-table-version-stamping limitation as joins. Both
are gated explicitly (cache silently bypassed; `.Join` throws).
- **`WhereExists` against the outer query's own table is rejected** — unqualified
inner columns would be ambiguous.
- **Soft-delete auto-filtering applies to single-table reads only** —
`mysql.Query<T>()` terminals and the repository getters. It does NOT apply to
joins, subqueries, or the query builder's bulk `UpdateAsync`/`DeleteAsync` (those
stay raw so you can target or restore deleted rows). `QueryBuilder.DeleteAsync`
is a hard delete regardless of `[SoftDelete]`.
- **Joins are not cacheable in this version.** The result cache stamps each entry
with a single table's version counter, so a join entry could not be invalidated
when the *other* joined table mutates. `.WithCache` / `.SmartCache` are
intentionally absent on `JoinedQuery` rather than risk serving stale joins;
multi-table invalidation is on the roadmap.
- **`TRight` must be specified explicitly** (e.g. `Join<Customer, long, OrderView>`)
— it cannot be inferred from a lambda parameter type.
## [4.5.0] — 2026-05-24
### Added
- **`StorageType` enum on `ColumnAttribute`.** Per-column physical storage
override that takes precedence over `DataType` for DDL generation.
Available values: `Binary`, `VarBinary`, `TinyBlob`, `Blob`, `MediumBlob`,
`LongBlob`. When set, the column is stored as the chosen binary type and
values are automatically converted to/from binary on read and write.
- **Guid-as-BINARY(16) support.** Set `StorageType = StorageType.Binary` on a
`Guid` property and CL.MySQL2 stores it as `BINARY(16)` using RFC 4122
big-endian byte layout for correct lexicographic sort order. Conversion is
automatic in all paths: insert, update, read, WHERE clauses, and IN queries.
```csharp
[Column(StorageType = StorageType.Binary, Primary = true, NotNull = true)]
public Guid Id { get; set; }
```
- **Automatic binary conversion for all CLR types.** Any property can be
stored as binary by setting `StorageType`. Supported types and their binary
sizes (auto-detected when `Size` is not explicit):
| CLR type | Binary size | Byte order |
|---|---|---|
| `Guid` | 16 | RFC 4122 big-endian |
| `long` / `ulong` | 8 | big-endian |
| `int` / `uint` | 4 | big-endian |
| `short` / `ushort` | 2 | big-endian |
| `double` | 8 | big-endian |
| `float` | 4 | big-endian |
| `decimal` | 16 | big-endian |
| `DateTime` / `DateTimeOffset` | 8 | ticks, big-endian |
| `bool` / `byte` / `sbyte` | 1 | — |
| `string` | explicit | UTF-8 |
| `byte[]` | passthrough | as-is |
- **LINQ WHERE support for binary-stored columns.** Expressions like
`repo.Where(x => x.Id == someGuid)` and `list.Contains(x.Id)` correctly
convert parameter values to binary when the column uses `StorageType`.
- **`SequentialGuid.NewId()` helper.** Generates time-ordered UUIDv7 values
optimized for `BINARY(16)` primary keys. Sequential inserts append to the
B-tree instead of causing random page splits — dramatically reducing index
fragmentation compared to random UUIDv4.
```csharp
[Column(StorageType = StorageType.Binary, Primary = true, NotNull = true)]
public Guid Id { get; set; } = SequentialGuid.NewId();
```
- **Unified versioning.** All CodeLogic.Libs now share a single version line
controlled by `version.txt`. AssemblyVersion is derived automatically.
### Notes
- **No breaking changes.** `StorageType` defaults to `StorageType.Default`
(the zero-value), so all existing entities and schemas are unaffected.
Guid inference still returns `Char(36)` unless you explicitly opt in.
## [4.2.3] — 2026-05-15
### Fixed
- **Cache orphan accumulation on mutations.** `QueryCache.Invalidate(tableName)`
previously only bumped the per-table version counter — old cache entries
(now unreachable via the read path because the cache key changed) lingered
in the underlying store until TTL or LRU swept them. On a busy app this
produced unbounded memory growth. Invalidate now also calls
`ICacheStore.EvictByTableAsync` to sweep matching entries in the same step.
- **SmartCachePool orphan tracking.** Each pool entry now remembers the
cache key it last wrote. If the next tick computes a different key
(because a mutation bumped the table version between ticks), the
previous key is evicted explicitly. Works on any `ICacheStore`
implementation including ones that can't enumerate (Redis without
SCAN, memcached).
### Added
- `ICacheStore.EvictByTableAsync(tableName)` — bulk eviction by table.
Default in-process implementation is an O(n) scan over current entries.
Distributed adapters can override (e.g. Redis tag-set or key prefix).
- `ICacheStore.CountByTable()` — entries grouped by tableName for diagnostics.
- `QueryCache.GetStats()` → `QueryCacheStats(TotalEntries, EntriesByTable,
TableVersions)`. Surfaced on the library API via `MySQL2Library.GetCacheStats()`
so admin tools can render "what's in the cache right now" without
dumping values.
## [4.2.2] — 2026-05-15
### Fixed
- **SmartCache pool no longer corrupts `ToListAsync` results.** The pool's
refresh factory stored the unwrapped `List<T>` instead of the
`Result<List<T>>` that the cache-aside read path expects. After the first
background refresh tick, every subsequent read failed the `(Result<List<T>>)`
cast inside `GetOrSetAsync`, the outer try/catch turned it into a Failure
Result, and callers saw an empty list (manifested as "No servers configured"
/ empty leaderboards roughly one refresh interval after warm-up). `FirstOrDefaultAsync`
and `CountAsync` already cached the full Result and were unaffected; only
`ToListAsync` was wrong.
## [4.2.1] — 2026-05-15
### Fixed
- **Failure Results no longer poison the cache.** Previously, a query that
failed (e.g. transient connection error during a cold warm-up) had its
`Result<T>.Failure` value cached just like a successful one — subsequent
reads served the failure until the entry's TTL expired or a pool refresh
overwrote it. Now `QueryCache.GetOrSetAsync` skips writing failure Results,
evicts any pre-existing failure entry on read, and `SetDirectAsync` (the
smart-cache pool's refresh path) refuses to write failures too. Empty
server lists / leaderboards on first request after a deploy are gone.
## [4.2.0] — 2026-05-15
### Added
- **Smart-cache pool warm-up on registration.** `RegisterCachePool` now
accepts an optional `warmUp: Func<Task>` callback that fires as a
fire-and-forget task right after the pool starts. The callback just
calls the queries that should be warm — they auto-register with the
pool via their normal `.SmartCache(name)` decoration — so the cache
is hot before the first user request hits it. Exceptions are caught
and logged; the pool stays lazy if warm-up fails.
- `SmartCachePool.WarmUp(Func<Task>)` — public method exposing the same
behaviour for callers that want to warm a pool independently of
registration.
## [4.1.2] — 2026-05-15
### Added
- **Smart cache pools** — named groups of cached queries kept warm by a
background timer (`mysql.RegisterCachePool("dashboard", refreshEvery: 30s)`,
opt in per-query with `.SmartCache("dashboard")`). Reads after the first
populate the cache never block on the DB — the pool's timer re-runs every
registered query in the background and overwrites the entry.
- `SmartCachePool.RefreshNowAsync()` — out-of-schedule refresh, useful right
after a deploy to prime the cache before the first user hits the page.
- `MySQL2Library.GetCachePoolStats()` — diagnostic snapshot per pool
(entry count, ticks fired, ticks failed, last tick UTC).
- `QueryCache.SetDirectAsync(...)` — internal cache write API used by pools.
### Notes
- Smart cache is mutually exclusive with `.WithCache(TimeSpan)` — if both are
set, the pool wins and the TTL comes from `refreshEvery * 2`.
- Unknown pool name on `.SmartCache(name)` logs a warning and falls back to
non-cached execution (no exception).
- Per-pool eviction policy: an entry that has not been read for
`MaxIdleFires` (default 3) consecutive ticks is dropped from the refresh
list. Bounds cardinality on parameterized queries.
- Smart cache is disabled inside a transaction scope (same as `.WithCache`).
- Single-node only in v4.2. Multi-node coordination is on the roadmap.
## [4.1.1] — 2026-04-17
### Fixed
- Qualify LHS columns in upsert SET clauses so `UpsertAsync` no longer
generates ambiguous column references when the table has columns whose
names clash with parameter placeholders.
## [4.1.0] — 2026-04-17
### Added
- **Typed upsert** — `UpsertAsync` + `UpsertWithIncrementsAsync` on the
repository. Compiles to `INSERT ... ON DUPLICATE KEY UPDATE ...` with
full LINQ-shaped value/increment expressions on the update side.
### Changed
- `LibraryManifest.Version` now reads from the assembly's `AssemblyVersion`
attribute at runtime instead of being a hard-coded string. Keeps the
manifest honest across rebuilds.
## [4.0.4] — 2026-04-16
### Changed
- README + manifest refresh across every CodeLogic library for the v4 baseline.
- No functional changes vs 4.0.3.
## [4.0.1] — 2026-04-09
### Fixed
- Drop the `Expression.Compile().DynamicInvoke()` fast-path inside the SQL
expression visitor — it broke on closures over generic types. The visitor
now always walks the tree.
## [4.0.0] — 2026-04-09
Major rewrite. Breaking.
### Added
- **Projection pushdown** — `.Select<TResult>(x => new { ... })` emits a
real `SELECT col1, col2, ...` column list instead of `SELECT *`. Combined
with compiled materializers this often cuts row-transfer bandwidth by 80%+.
- **SQL-side aggregation** — `.GroupBy(...).Select(g => new { g.Key,
g.Sum(...), g.Average(...), ... })` translates to real `GROUP BY` +
aggregate functions. No client-side row materialization.
- **`SqlFn` helpers** — server-side function markers (`SqlFn.DayOfWeek`,
`SqlFn.Hour`, `SqlFn.BucketUtc`, `SqlFn.Coalesce`, `SqlFn.Round`, etc.)
recognized by the translator — mirrors EF's `EF.Functions` pattern.
- **`[Index]` attribute** — declare named, unique, and covering indexes
(with `Include = new[] { ... }`) at the column level.
- **`[RetainDays]` attribute** — opt entities into a daily background purge
worker that runs batched `DELETE` until drained.
- **Working result cache** — `.WithCache(TimeSpan)` with two correctness
fixes from prior versions:
- DateTime closures near `UtcNow` are time-quantized to a configurable
window (default 60s) so `.Where(x => x.At >= UtcNow.AddDays(-30))`
stops producing a unique cache key per call.
- Mutations bump a per-table version that participates in the cache
key — invalidation is free (old keys become un-hittable, no eviction
loop).
- **`EntityMetadata<T>` + compiled `Materializer<T>`** — reflection runs
once per entity at first use; subsequent reads use a compiled
reader-to-entity function.
- **Observability events** — `QueryExecutedEvent`, `SlowQueryEvent`,
`CacheHitEvent`, `CacheMissEvent`, `N1QueryDetectedEvent`,
`TableSyncedEvent` publish to the CodeLogic event bus.
- **`MaxBatchInsertSize`, `MaxInClauseValues`, `PreparedStatementCacheSize`,
`N1DetectorThreshold`, `CaptureExplainOnSlowQuery`, `DefaultStringSize`,
`CacheEnabledOverride`** — per-database config knobs.
- **`CacheConfiguration`** — global cache settings (`Enabled`,
`MaxEntries`, `DefaultTtlSeconds`, `TimeQuantizeSeconds`,
`PublishEvents`).
### Changed
- Republished as v4.0.0 to reset the version line with the new package shape.
- All public APIs refreshed under the v4 baseline.
## Earlier releases
Pre-4.0 history is retained in the
[git log](https://github.com/Media2A/CodeLogic.Libs/commits/main/CL.MySQL2)
but is not documented in detail here — the library shape changed
significantly in the v4 rewrite.